MySQL8.0から、Descending Indexがサポートされる。
これによりMySQL5.7と比較して、大きなテーブルでの降順(DESC)のデータ参照処理を、高速に実行する事が出来るようになる。小さいテーブルに関しては、既存のMySQL5.7でもインデックスが利用出来るので、それ程差は出ないかと思います。

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXはASCで作成されている)


mysql> select @@version;                                              
+-----------+
| @@version |
+-----------+
| 8.0.1-dmr |
+-----------+
1 row in set (0.00 sec)

mysql> show create table rental\G                                     
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3; 
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXをDESCで作成し直した場合)
インデックスを降順に作成したので、先程とはEXTRAが反対になっている事が確認出来る。

mysql> alter table rental add unique key rental_date_desc (`rental_date` desc,`inventory_id` desc,`customer_id` desc);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;                                                
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;                                               
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> 

詳細は、以下のMySQL Server Teamのブログを確認下さい。
MySQL 8.0 Labs – Descending Indexes in MySQL
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

MySQL5.5 ~ MySQL5.7までは昇順(ASC)で格納されたインデックスデータを利用してASC, DESC共に参照処理を行う
MySQL 5.7までは、インデックスのデータはASCで格納されています。こちらは、DESCで格納したインデックスと比べて、降順のデータの参照パフォーマンスは遅いが、
クエリーにDESCオプションを付けて、直近のデータを参照するとASCで作成したINDEXを利用してデータを参照する為、インデックスを利用した後方参照処理になる為、
インデックスが無い場合と比べても高速なレスポンスで後方参照処理を行う事が出来る。以下のマニュアルは、若干分かり難いがASC、DESCを付けてインデックスを作成しても、
MySQL5.5以降のMySQLであれば昇順(ASC)で作成されたINDEXでASCもDESCも処理出来る為、高速に参照する事が可能です。MySQL8.0で処理がより高速になる。

5.7 英語マニュアル
https://dev.mysql.com/doc/refman/5.7/en/create-index.html
An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

5.6 日本語マニュアル
https://dev.mysql.com/doc/refman/5.6/ja/create-index.html
index_col_name の指定を ASC または DESC で終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。

MySQL5.7.18の実行プランは以下のような感じです。インデックスを利用して降順(DESC)参照が行われている事が確認出来る。
MySQL8.0RCがリリースされた頃に、大きなテーブルでレスポンスの差を確認して見たいと思います。


mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.18-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table rental\G
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> select * from rental limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental limit 3;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16005 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` limit 3

mysql> select * from rental order by rental_date asc limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` limit 3

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` desc limit 3
mysql>  


最近、MySQL5.7の形態素解析エンジンのmecabに関して辞書をカスタマイズ出来るかどうか
聞かれる事が増えた為、mecabの辞書をカスタマイズしてきちんと動作確認してみました。
結論としては、問題無くカスタマイズした辞書も利用出来るようです。
但し、MySQLのマニュアルページを見る限り、MySQL Binaryから提供されているmecab辞書の利用を推奨しているようです。

【以下、抜粋】
Installing MeCab From Source (Optional)
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-mecab.html
The mecab and mecab-ipadic packages distributed with the MySQL binary are recommended but
if you want to build mecab and mecab-ipadic from source, basic installation steps are provided below.
For additional information, refer to the MeCab documentation.

前回のmecab基本検証
http://variable.jp/2015/04/10/creating-index-with-utf8mb4-on-mecab-with-5-7-7/

基本的には、MySQLのmecab辞書は特に手を加えていないようです
MySQLのコンパイル済みTarからインストールした場合のファイル

 
[root@misc02 admin]# ls -l /usr/local/mysql/lib/mecab/dic/ipadic_utf-8
合計 51828
-rw-r--r--. 1 root mysql   262496 11月  6 23:18 char.bin
-rw-r--r--. 1 root mysql      693 11月  6 23:18 dicrc
-rw-r--r--. 1 root mysql    55910 11月  6 23:18 left-id.def
-rw-r--r--. 1 root mysql  3463716 11月  6 23:18 matrix.bin
-rw-r--r--. 1 root mysql     1477 11月  6 23:18 pos-id.def
-rw-r--r--. 1 root mysql     6241 11月  6 23:18 rewrite.def
-rw-r--r--. 1 root mysql    55910 11月  6 23:18 right-id.def
-rw-r--r--. 1 root mysql 49199027 11月  6 23:18 sys.dic
-rw-r--r--. 1 root mysql     5690 11月  6 23:18 unk.dic

MecabサイトからIPA辞書をダウンロードしてインストールした場合(手を加えていない場合)

[root@misc02 admin]# ls -l /usr/local/lib/mecab/dic/ipadic
合計 51828
-rw-r--r--. 1 root root   262496  4月  4 11:36 char.bin
-rw-r--r--. 1 root root      693  4月  4 11:36 dicrc
-rw-r--r--. 1 root root    55910  4月  4 11:36 left-id.def
-rw-r--r--. 1 root root  3463716  4月  4 11:36 matrix.bin
-rw-r--r--. 1 root root     1477  4月  4 11:36 pos-id.def
-rw-r--r--. 1 root root     6241  4月  4 11:36 rewrite.def
-rw-r--r--. 1 root root    55910  4月  4 11:36 right-id.def
-rw-r--r--. 1 root root 49199027  4月  4 11:36 sys.dic
-rw-r--r--. 1 root root     5690  4月  4 11:36 unk.dic
[root@misc02 admin]# 

ここから、実際に辞書を登録して新規ワードが追加された辞書を登録しています。
※ 事前にnkf等で辞書をUTF-8に変換していますが、ここでは説明を割愛してあります。

【辞書の追加】2つの名詞のみを登録
今回は、”日本オラクル株式会社”と”mecabプラグイン”の二つのみ登録して確認

[root@misc02 mecab]# cat user_defined_dictionary_utf8.csv 
日本オラクル株式会社,1285,1285,5078,名詞,一般,*,*,*,*,日本オラクル株式会社,ニホンオラクルカブシキガイシャ,ニホンオラクルカブシキガイシャ
mecabプラグイン,1285,1285,5699,名詞,一般,*,*,*,*,mecabプラグイン,メカブプラグイン,メカブプラグイン,和布蕪プラグイン
[root@misc02 mecab]# cp -rp user_defined_dictionary_utf8.csv mecab-ipadic-2.7.0-20070801/
[root@misc02 mecab]#

user_defined_dictionary_utf8.csvが作成した追加の辞書ファイル
2文字のみ登録しているので、2文字インデックシングされた事が以下のアウトプットから確認出来る。

[root@misc02 mecab-ipadic-2.7.0-20070801]# /usr/local/libexec/mecab/mecab-dict-index -f utf-8 -t utf-8
reading ./unk.def ... 40
emitting double-array: 100% |###########################################| 
./model.def is not found. skipped.
reading ./Adj.csv ... 27210
reading ./Adnominal.csv ... 135
reading ./Adverb.csv ... 3032
reading ./Auxil.csv ... 199
reading ./Conjunction.csv ... 171
reading ./Filler.csv ... 19
reading ./Interjection.csv ... 252
reading ./Noun.adjv.csv ... 3328
reading ./Noun.adverbal.csv ... 795
reading ./Noun.csv ... 60477
reading ./Noun.demonst.csv ... 120
reading ./Noun.nai.csv ... 42
reading ./Noun.name.csv ... 34202
reading ./Noun.number.csv ... 42
reading ./Noun.org.csv ... 16668
reading ./Noun.others.csv ... 151
reading ./Noun.place.csv ... 72999
reading ./Noun.proper.csv ... 27327
reading ./Noun.verbal.csv ... 12146
reading ./Others.csv ... 2
reading ./Postp-col.csv ... 91
reading ./Postp.csv ... 146
reading ./Prefix.csv ... 221
reading ./Suffix.csv ... 1393
reading ./Symbol.csv ... 208
reading ./Verb.csv ... 130750
reading ./user_defined_dictionary_utf8.csv ... 2
emitting double-array: 100% |###########################################| 
reading ./matrix.def ... 1316x1316
emitting matrix      : 100% |###########################################| 

done!
[root@misc02 mecab-ipadic-2.7.0-20070801]# make install
make[1]: ディレクトリ `/home/admin/mecab/mecab-ipadic-2.7.0-20070801' に入ります
make[1]: `install-exec-am' に対して行うべき事はありません.
/bin/sh ./mkinstalldirs /usr/local/lib/mecab/dic/ipadic
 /bin/install -c -m 644 ./matrix.bin /usr/local/lib/mecab/dic/ipadic/matrix.bin
 /bin/install -c -m 644 ./char.bin /usr/local/lib/mecab/dic/ipadic/char.bin
 /bin/install -c -m 644 ./sys.dic /usr/local/lib/mecab/dic/ipadic/sys.dic
 /bin/install -c -m 644 ./unk.dic /usr/local/lib/mecab/dic/ipadic/unk.dic
 /bin/install -c -m 644 ./left-id.def /usr/local/lib/mecab/dic/ipadic/left-id.def
 /bin/install -c -m 644 ./right-id.def /usr/local/lib/mecab/dic/ipadic/right-id.def
 /bin/install -c -m 644 ./rewrite.def /usr/local/lib/mecab/dic/ipadic/rewrite.def
 /bin/install -c -m 644 ./pos-id.def /usr/local/lib/mecab/dic/ipadic/pos-id.def
 /bin/install -c -m 644 ./dicrc /usr/local/lib/mecab/dic/ipadic/dicrc
make[1]: ディレクトリ `/home/admin/mecab/mecab-ipadic-2.7.0-20070801' から出ます
[root@misc02 mecab-ipadic-2.7.0-20070801]# 

mecab辞書を追加前
辞書追加前は、文字が分割されて辞書登録されている

[root@misc02 mecab-ipadic-2.7.0-20070801]# echo '日本オラクル株式会社' | /usr/local/bin/mecab
日本    名詞,固有名詞,地域,国,*,*,日本,ニッポン,ニッポン
オラクル        名詞,一般,*,*,*,*,*
株式会社        名詞,一般,*,*,*,*,株式会社,カブシキガイシャ,カブシキガイシャ
EOS
[root@misc02 mecab-ipadic-2.7.0-20070801]# 

[root@misc02 mecab-ipadic-2.7.0-20070801]# echo 'mecabプラグイン' | /usr/local/bin/mecab
mecab   名詞,固有名詞,組織,*,*,*,*
プラグ  名詞,一般,*,*,*,*,プラグ,プラグ,プラグ
イン    名詞,一般,*,*,*,*,イン,イン,イン
EOS
[root@misc02 mecab-ipadic-2.7.0-20070801]# 

mecab辞書を追加後
きちんと追加したWORDが1つの単語として認識されている事が分かる

[root@misc02 mecab]# echo '日本オラクル株式会社' | /usr/local/bin/mecab
日本オラクル株式会社    名詞,一般,*,*,*,*,日本オラクル株式会社,ニホンオラクルカブシキガイシャ,ニホンオラクルカブシキガイシャ
EOS
[root@misc02 mecab]# echo 'mecabプラグイン' | /usr/local/bin/mecab
mecabプラグイン 名詞,一般,*,*,*,*,mecabプラグイン,メカブプラグイン,メカブプラグイン,和布蕪プラグイン
EOS
[root@misc02 mecab]# 

検索対象文字列
Server1とServer2で同じデータを登録してIndexingしてあります。
また、mecab辞書はmy.cnfで新規作成したmecab辞書を参照するように設定済み。

root@localhost [mecab]> select description from articles where description like '%mecab%';
+-------------------------------------------------------------------------+
| description                                                             |
+-------------------------------------------------------------------------+
| MySQL mecabプラグインの辞書を追加した場合の動作確認                     |
| MySQL mecab                                                             |
+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [mecab]> select description from articles where description like '%日本%';
+---------------------------------------------------------------------------+
| description                                                               |
+---------------------------------------------------------------------------+
| 日本オラクル株式会社 辞書を追加した場合の動作確認                         |
| 日本オラクル                                                              |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [mecab]> 

オリジナルのmecab辞書
新規登録した単語では認識されていない。オリジナル辞書にあるmecabや日本と言った辞書で確認出来ている。

root@localhost [mecab]> OPTIMIZE TABLE articles;
+----------------+----------+----------+----------+
| Table          | Op       | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| mecab.articles | optimize | status   | OK       |
+----------------+----------+----------+----------+
1 row in set (0.01 sec)

root@localhost [mecab]> SELECT word, doc_count, doc_id, position FROM information_schema.INNODB_FT_INDEX_TABLE where word like 'meca%';
+-------+-----------+--------+----------+
| word  | doc_count | doc_id | position |
+-------+-----------+--------+----------+
| mecab |         1 |     33 |        5 |
| mecab |         1 |     35 |        5 |
+-------+-----------+--------+----------+
2 rows in set (0.00 sec)

root@localhost [mecab]> SELECT word, doc_count, doc_id, position FROM information_schema.INNODB_FT_INDEX_TABLE where word like 'mecabプラグイン';
Empty set (0.01 sec)

root@localhost [mecab]> SELECT word, doc_count, doc_id, position FROM information_schema.INNODB_FT_INDEX_TABLE where word like '日本%';
+--------+-----------+--------+----------+
| word   | doc_count | doc_id | position |
+--------+-----------+--------+----------+
| 日本   |         1 |     34 |        0 |
| 日本   |         1 |     36 |        0 |
+--------+-----------+--------+----------+
2 rows in set (0.00 sec)

root@localhost [mecab]> SELECT word, doc_count, doc_id, position FROM information_schema.INNODB_FT_INDEX_TABLE where word like '日本オラクル%';
Empty set (0.01 sec)

root@localhost [mecab]> 
 

WORDを追加したmecab辞書
追加で単語が登録されているので、追加登録した辞書で文字列が検索出来ている。
サービスのニーズや流行りの言葉を登録させておく事により、ビジネスのニーズに柔軟に対応出来そうです。
大規模なサービスはSolr等の方が良いかもしれませんが、小規模~中規模のサービスには使えそうです。

root@localhost [mecab]> OPTIMIZE TABLE articles;
+----------------+----------+----------+----------+
| Table          | Op       | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| mecab.articles | optimize | status   | OK       |
+----------------+----------+----------+----------+
1 row in set (0.01 sec)

root@localhost [mecab]>  SELECT word, doc_count, doc_id, position FROM information_schema.INNODB_FT_INDEX_TABLE where word like 'meca%';
+----------------------+-----------+--------+----------+
| word                 | doc_count | doc_id | position |
+----------------------+-----------+--------+----------+
| mecab                |         1 |     20 |        5 |
| mecabプラグイン      |         1 |     18 |        5 |
+----------------------+-----------+--------+----------+
2 rows in set (0.01 sec)

root@localhost [mecab]> SELECT word, doc_count, doc_id, position FROM information_schema.INNODB_FT_INDEX_TABLE where word like 'mecabプラグイン';
+----------------------+-----------+--------+----------+
| word                 | doc_count | doc_id | position |
+----------------------+-----------+--------+----------+
| mecabプラグイン      |         1 |     18 |        5 |
+----------------------+-----------+--------+----------+
1 row in set (0.00 sec)

root@localhost [mecab]> SELECT word, doc_count, doc_id, position FROM information_schema.INNODB_FT_INDEX_TABLE where word like '日本%';
+--------------------------------+-----------+--------+----------+
| word                           | doc_count | doc_id | position |
+--------------------------------+-----------+--------+----------+
| 日本                           |         1 |     21 |        0 |
| 日本オラクル株式会社           |         1 |     19 |        0 |
+--------------------------------+-----------+--------+----------+
2 rows in set (0.01 sec)

root@localhost [mecab]> SELECT word, doc_count, doc_id, position FROM information_schema.INNODB_FT_INDEX_TABLE where word like '日本オラクル%';
+--------------------------------+-----------+--------+----------+
| word                           | doc_count | doc_id | position |
+--------------------------------+-----------+--------+----------+
| 日本オラクル株式会社           |         1 |     19 |        0 |
+--------------------------------+-----------+--------+----------+
1 row in set (0.01 sec)

root@localhost [mecab]> 

search_output

参考:
12.9.9 MeCab Full-Text Parser Plugin
MeCab: Yet Another Part-of-Speech and Morphological Analyzer
単語の追加方法


mecabプラグイン
MySQL5.7からmecabプラグインが利用出来るようになるので、基本的な動作確認をしました。
現状では、eucjpms (ujis), cp932 (sjis), and utf8 (utf8mb4)のみ対応している状態です。

mecab-pug

Mecabについて
http://ja.wikipedia.org/wiki/MeCab

参考: 
InnoDB Full-Text: MeCab Parser
http://planet.mysql.com/entry/?id=5989321

初期設定
Plugin Directoryにモジュールや設定ファイルがあります。

[root@misc01 mecab]# ls -l /usr/local/mysql/lib/
合計 800168
-rw-r--r--. 1 root mysql  21934486  2月 27 00:04 libmysqlclient.a
lrwxrwxrwx. 1 root mysql        20  2月 27 00:12 libmysqlclient.so -> libmysqlclient.so.20
lrwxrwxrwx. 1 root mysql        24  2月 27 00:12 libmysqlclient.so.20 -> libmysqlclient.so.20.0.0
-rwxr-xr-x. 1 root mysql  10071218  2月 27 00:04 libmysqlclient.so.20.0.0
lrwxrwxrwx. 1 root mysql        16  2月 27 00:12 libmysqlclient_r.a -> libmysqlclient.a
lrwxrwxrwx. 1 root mysql        17  2月 27 00:12 libmysqlclient_r.so -> libmysqlclient.so
lrwxrwxrwx. 1 root mysql        20  2月 27 00:12 libmysqlclient_r.so.20 -> libmysqlclient.so.20
lrwxrwxrwx. 1 root mysql        24  2月 27 00:12 libmysqlclient_r.so.20.0.0 -> libmysqlclient.so.20.0.0
-rw-r--r--. 1 root mysql 347907574  2月 27 00:03 libmysqld-debug.a
-rw-r--r--. 1 root mysql 438661070  2月 27 00:12 libmysqld.a
-rw-r--r--. 1 root mysql     27154  2月 27 00:03 libmysqlservices.a
-rw-r--r--. 1 root mysql    754332  2月 26 23:58 libtcmalloc_minimal.so
drwxr-xr-x. 4 root mysql        26  3月 28 08:30 mecab
drwxr-xr-x. 3 root mysql      4096  3月 28 08:30 plugin
[root@misc01 mecab]# ls -l
合計 0
drwxr-xr-x. 5 root mysql 63  3月 28 08:30 dic
drwxr-xr-x. 2 root mysql 20  3月 29 07:29 etc
[root@misc01 mecab]# pwd
/usr/local/mysql/lib/mecab
[root@misc01 mecab]# ls -l dic/
合計 12
drwxr-xr-x. 2 root mysql 4096  3月 28 08:30 ipadic_euc-jp
drwxr-xr-x. 2 root mysql 4096  3月 28 08:30 ipadic_sjis
drwxr-xr-x. 2 root mysql 4096  3月 28 08:30 ipadic_utf-8
[root@misc01 mecab]# ls -l etc/
合計 4
-rw-r--r--. 1 root mysql 379  3月 29 07:29 mecabrc
[root@misc01 mecab]# 

基本インストールと設定
mecab設定ファイル

[root@misc01 etc]# cat mecabrc 
; Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
;
; Configuration file of MeCab
;
; We have three ipadics in /path/to/mysql/lib/mecab/dic:
; ipadic_euc-jp, ipadic_sjis, and ipadic_utf8.
; you can choose any of them or use your own one.
dicdir =  /path/to/mysql/lib/mecab/lib/mecab/dic/ipadic_euc-jp
[root@misc01 etc]# vi mecabrc 
[root@misc01 etc]# cat mecabrc 
; Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
;
; Configuration file of MeCab
;
; We have three ipadics in /path/to/mysql/lib/mecab/dic:
; ipadic_euc-jp, ipadic_sjis, and ipadic_utf8.
; you can choose any of them or use your own one.
; dicdir =  /path/to/mysql/lib/mecab/lib/mecab/dic/ipadic_euc-jp
dicdir = /usr/local/mysql/lib/mecab/dic/ipadic_utf-8
[root@misc01 etc]# 


[root@misc01 plugin]# /usr/local/mysql/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.6-m16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> show variables like 'plug%';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';
Query OK, 0 rows affected (0.29 sec)

root@localhost [(none)]> SHOW STATUS LIKE 'mecab_charset';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| mecab_charset | utf8  |
+---------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> show variables like 'innodb_ft_min_token_size';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 2     |
+--------------------------+-------+
1 row in set (0.00 sec)


root@localhost [(none)]> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_LICENSE from information_schema.plugins
    -> where PLUGIN_NAME = 'meCab';
+-------------+----------------+----------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_LICENSE |
+-------------+----------------+----------------+
| mecab       | 0.1            | GPL            |
+-------------+----------------+----------------+
1 row in set (0.00 sec)

root@localhost [(none)]> 

install

検証用データベース、テーブル、データの作成

root@localhost [(none)]> CREATE DATABASE `mecab` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use mecab
Database changed
root@localhost [mecab]> CREATE TABLE M_DEMO
    ->  (
    ->   FTS_M_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(100),
    ->   FULLTEXT INDEX mecab_idx(title) WITH PARSER mecab
    ->   ) Engine=InnoDB CHARACTER SET utf8;
Query OK, 0 rows affected (0.15 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('東京都は日本の首都です'),('京都と大阪は日本の府です');
 
Query OK, 2 rows affected (0.69 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('mysql');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('MYSQL');
Query OK, 1 row affected (0.01 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('MySQL');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('まいえすきゅーえる');
Query OK, 1 row affected (0.01 sec)

root@localhost [mecab]> 


root@localhost [mecab]> select * from M_DEMO;
+----------+--------------------------------------+
| FTS_M_ID | title                                |
+----------+--------------------------------------+
|        1 | 東京都は日本の首都です               |
|        2 | 京都と大阪は日本の府です             |
|        3 | mysql                                |
|        4 | MYSQL                                |
|        5 | MySQL                                |
|        6 | マイエスキューエル                   |
|        7 | マイエスキューエル                            |
|        8 | まいえすきゅーえる                   |
+----------+--------------------------------------+
8 rows in set (0.00 sec)

root@localhost [mecab]> SET GLOBAL innodb_ft_aux_table="mecab/M_DEMO";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mecab]> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;
+-----------------------------+--------------+-------------+-----------+--------+----------+
| WORD                        | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-----------------------------+--------------+-------------+-----------+--------+----------+
| 東京                        |            2 |           2 |         1 |      2 |        0 |
| 日本                        |            2 |           3 |         2 |      2 |       12 |
| 首都                        |            2 |           2 |         1 |      2 |       21 |
| です                        |            2 |           3 |         2 |      2 |       27 |
| 京都                        |            3 |           3 |         1 |      3 |        0 |
| 大阪                        |            3 |           3 |         1 |      3 |        9 |
| 日本                        |            2 |           3 |         2 |      3 |       18 |
| です                        |            2 |           3 |         2 |      3 |       30 |
| mysql                       |            4 |           6 |         3 |      4 |        0 |
| mysql                       |            4 |           6 |         3 |      5 |        0 |
| mysql                       |            4 |           6 |         3 |      6 |        0 |
| マイエスキューエル          |            7 |           7 |         1 |      7 |        0 |
| マイエスキューエル                   |            8 |           8 |         1 |      8 |        0 |
| いえ                        |            9 |           9 |         1 |      9 |        3 |
| すき                        |            9 |           9 |         1 |      9 |        9 |
| える                        |            9 |           9 |         1 |      9 |       21 |
+-----------------------------+--------------+-------------+-----------+--------+----------+
16 rows in set (0.00 sec)

root@localhost [mecab]> 

mecab

■ IN NATURAL LANGUAGE MODE(Text Searches)
Text searched for is converted to a union of search tokens.
  For example, ‘日本の首都’ is converted to ‘日本 の 首都’.

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本の首都' IN NATURAL LANGUAGE MODE);
+----------+--------------------------------------+
| FTS_M_ID | title                                |
+----------+--------------------------------------+
|        1 | 東京都は日本の首都です               |
|        2 | 京都と大阪は日本の府です             |
+----------+--------------------------------------+
2 rows in set (0.00 sec)

root@localhost [mecab]>  SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都' IN NATURAL LANGUAGE MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+--------------------+
| FTS_M_ID | title                                | score              |
+----------+--------------------------------------+--------------------+
|        1 | 東京都は日本の首都です               |  1.178047776222229 |
|        2 | 京都と大阪は日本の府です             | 0.3624762296676636 |
|        3 | mysql                                |                  0 |
|        4 | MYSQL                                |                  0 |
|        5 | MySQL                                |                  0 |
|        6 | マイエスキューエル                   |                  0 |
|        7 | マイエスキューエル                            |                  0 |
|        8 | まいえすきゅーえる                   |                  0 |
+----------+--------------------------------------+--------------------+
8 rows in set (0.00 sec)

■ IN BOOLEAN MODE(Text Searches)
Text searched for is converted to a phrase search.
  For example, ‘日本の首都’ is converted to ‘”日本 の 首都”‘.

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本の首都' IN BOOLEAN MODE);
+----------+-----------------------------------+
| FTS_M_ID | title                             |
+----------+-----------------------------------+
|        1 | 東京都は日本の首都です            |
+----------+-----------------------------------+
1 row in set (0.00 sec)

root@localhost [mecab]> SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+-------------------+
| FTS_M_ID | title                                | score             |
+----------+--------------------------------------+-------------------+
|        1 | 東京都は日本の首都です               | 1.178047776222229 |
|        2 | 京都と大阪は日本の府です             |                 0 |
|        3 | mysql                                |                 0 |
|        4 | MYSQL                                |                 0 |
|        5 | MySQL                                |                 0 |
|        6 | マイエスキューエル                   |                 0 |
|        7 | マイエスキューエル                            |                 0 |
|        8 | まいえすきゅーえる                   |                 0 |
+----------+--------------------------------------+-------------------+
8 rows in set (0.00 sec)

mecab_cost

■ IN BOOLEAN MODE(Wildcard Searches)
No tokenization for the text of a wildcard search.
For example, for ‘日本の首都*’ we will search the prefix of ‘日本の首都’

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本*' IN BOOLEAN MODE);
+----------+--------------------------------------+
| FTS_M_ID | title                                |
+----------+--------------------------------------+
|        1 | 東京都は日本の首都です               |
|        2 | 京都と大阪は日本の府です             |
+----------+--------------------------------------+
2 rows in set (0.01 sec)

root@localhost [mecab]> SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本*' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+--------------------+
| FTS_M_ID | title                                | score              |
+----------+--------------------------------------+--------------------+
|        1 | 東京都は日本の首都です               | 0.3624762296676636 |
|        2 | 京都と大阪は日本の府です             | 0.3624762296676636 |
|        3 | mysql                                |                  0 |
|        4 | MYSQL                                |                  0 |
|        5 | MySQL                                |                  0 |
|        6 | マイエスキューエル                   |                  0 |
|        7 | マイエスキューエル                            |                  0 |
|        8 | まいえすきゅーえる                   |                  0 |
+----------+--------------------------------------+--------------------+
8 rows in set (0.00 sec)

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本の首都' IN BOOLEAN MODE);
+----------+-----------------------------------+
| FTS_M_ID | title                             |
+----------+-----------------------------------+
|        1 | 東京都は日本の首都です            |
+----------+-----------------------------------+
1 row in set (0.00 sec)

root@localhost [mecab]> SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+-------------------+
| FTS_M_ID | title                                | score             |
+----------+--------------------------------------+-------------------+
|        1 | 東京都は日本の首都です               | 1.178047776222229 |
|        2 | 京都と大阪は日本の府です             |                 0 |
|        3 | mysql                                |                 0 |
|        4 | MYSQL                                |                 0 |
|        5 | MySQL                                |                 0 |
|        6 | マイエスキューエル                   |                 0 |
|        7 | マイエスキューエル                            |                 0 |
|        8 | まいえすきゅーえる                   |                 0 |
+----------+--------------------------------------+-------------------+
8 rows in set (0.00 sec)


root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本の首都*' IN BOOLEAN MODE);
Empty set (0.00 sec)

root@localhost [mecab]>  SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都*' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+-------+
| FTS_M_ID | title                                | score |
+----------+--------------------------------------+-------+
|        1 | 東京都は日本の首都です               |     0 |
|        2 | 京都と大阪は日本の府です             |     0 |
|        3 | mysql                                |     0 |
|        4 | MYSQL                                |     0 |
|        5 | MySQL                                |     0 |
|        6 | マイエスキューエル                   |     0 |
|        7 | マイエスキューエル                            |     0 |
|        8 | まいえすきゅーえる                   |     0 |
+----------+--------------------------------------+-------+
8 rows in set (0.00 sec)

root@localhost [mecab]> 

■ IN BOOLEAN MODE(Phrase Searches)
A phrase search is tokenized by mecab. For example,
“日本の首都” is converted to “日本 の 首都”.

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('"日本の首都"' IN BOOLEAN MODE);
+----------+-----------------------------------+
| FTS_M_ID | title                             |
+----------+-----------------------------------+
|        1 | 東京都は日本の首都です            |
+----------+-----------------------------------+
1 row in set (0.00 sec)

root@localhost [mecab]>  SELECT FTS_M_ID,title,MATCH (title) AGAINST('"日本の首都"' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+-------------------+
| FTS_M_ID | title                                | score             |
+----------+--------------------------------------+-------------------+
|        1 | 東京都は日本の首都です               | 1.178047776222229 |
|        2 | 京都と大阪は日本の府です             |                 0 |
|        3 | mysql                                |                 0 |
|        4 | MYSQL                                |                 0 |
|        5 | MySQL                                |                 0 |
|        6 | マイエスキューエル                   |                 0 |
|        7 | マイエスキューエル                            |                 0 |
|        8 | まいえすきゅーえる                   |                 0 |
+----------+--------------------------------------+-------------------+
8 rows in set (0.00 sec)

root@localhost [mecab]> 

一つ前のPOSTで検証したデータでそのまま、NGRAMにてIN BOOLEAN MODEを使い検索してみました。

Natural Language
自然言語処理
By default or with the IN NATURAL LANGUAGE MODE modifier,
the MATCH() function performs a natural language search for a string against a text collection.
– text searched for is converted to a union of n-gram values. For example, ‘sql’ is converted to ‘sq ql’ (with a default token size of 2 or bigram).

natu

Boolean
真(true)と偽(false)の2種類の値だけを扱う型 (“+”、”-“で条件設定可能)
MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier.
With this modifier, certain characters have special meaning at the beginning or end of words in the search string.
– text searched for is converted to an n-gram phrase search. For example, ‘sql’ is converted to ‘”sq ql”‘:

bool

■ NATURAL LANGUAGE MODEとBOOLEAN MODEでの結果の違い

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
|        9 | sq    |
|       11 | ql    |
+----------+-------+
5 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('sql' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> 

compare

■以下,BOOLEANモードの挙動についての確認
– text searched
– wildcard searches
– phrase searches

text searched for is converted to an n-gram phrase search.
For example, ‘sql’ is converted to ‘”sq ql”‘:

12.9.2 Boolean Full-Text Searches
http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

基本設定と検証用データ

root@localhost [ngram]> show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+
1 row in set (0.00 sec)

root@localhost [ngram]> select * from N_DEMO;
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        1 | mysql                       |
|        2 | MYSQL                       |
|        3 | MySQL                       |
|        4 | マイエスキューエル          |
|        5 | マイエスキューエル                   |
|        6 | まいえすきゅーえる          |
|        7 | まい                        |
|        8 | えす                        |
|        9 | sq                          |
|       10 | sl                          |
|       11 | ql                          |
+----------+-----------------------------+
11 rows in set (0.00 sec)

■ example)
‘s*’はsを含むすべてのデータ


root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('s*' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
|        9 | sq    |
|       10 | sl    |
+----------+-------+
5 rows in set (0.00 sec)

root@localhost [ngram]> 

‘sq*’ is converted to ‘”sq”‘

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('sq*' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
|        9 | sq    |
+----------+-------+
4 rows in set (0.00 sec)

root@localhost [ngram]> 

‘sql*’ is equivalent to ‘”sq ql”‘:

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('sql*' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"sq ql"' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> 

■ データを追加して日本語で確認
挙動は同じで、対象とするデータを抽出出来る事を確認しました。
データを増やして、もっと試してみる必要あり。

root@localhost [ngram]> select * from N_DEMO;
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        1 | mysql                          |
|        2 | MYSQL                          |
|        3 | MySQL                          |
|        4 | マイエスキューエル             |
|        5 | マイエスキューエル                      |
|        6 | まいえすきゅーえる             |
|        7 | まい                           |
|        8 | えす                           |
|        9 | sq                             |
|       10 | sl                             |
|       11 | ql                             |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
13 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('ま*' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|        7 | まい                           |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
4 rows in set (0.01 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('まい*' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|        7 | まい                           |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
4 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('まいえ*' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
2 rows in set (0.01 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"まい いえ"' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
2 rows in set (0.01 sec)

root@localhost [ngram]> 

補足:AGAINST(‘まいえ*’ IN BOOLEAN MODE) = AGAINST(‘”まい いえ”‘ IN BOOLEAN MODE)である事をスコアで確認。

root@localhost [ngram]> SELECT FTS_N_ID,MATCH (title) AGAINST('まいえ*' IN BOOLEAN MODE) AS score FROM N_DEMO;
+----------+--------------------+
| FTS_N_ID | score              |
+----------+--------------------+
|        1 |                  0 |
|        2 |                  0 |
|        3 |                  0 |
|        4 |                  0 |
|        5 |                  0 |
|        6 | 0.9228526949882507 |
|        7 |                  0 |
|        8 |                  0 |
|        9 |                  0 |
|       10 |                  0 |
|       11 |                  0 |
|       12 |                  0 |
|       13 | 0.9228526949882507 |
+----------+--------------------+
13 rows in set (0.00 sec)

root@localhost [ngram]> SELECT FTS_N_ID,MATCH (title) AGAINST('"まい いえ"' IN BOOLEAN MODE) AS score FROM N_DEMO;
+----------+--------------------+
| FTS_N_ID | score              |
+----------+--------------------+
|        1 |                  0 |
|        2 |                  0 |
|        3 |                  0 |
|        4 |                  0 |
|        5 |                  0 |
|        6 | 0.9228526949882507 |
|        7 |                  0 |
|        8 |                  0 |
|        9 |                  0 |
|       10 |                  0 |
|       11 |                  0 |
|       12 |                  0 |
|       13 | 0.9228526949882507 |
+----------+--------------------+
13 rows in set (0.01 sec)

root@localhost [ngram]> 

score

その他、追加動作確認

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"mysql"' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.01 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"sql"' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"mysql"' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"きゅー"' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
3 rows in set (0.00 sec)

root@localhost [ngram]> root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"きゅーえ"' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|       12 | まいーえすきゅーえる           |
|        6 | まいえすきゅーえる             |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
3 rows in set (0.01 sec)

root@localhost [ngram]> root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('きゅー' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
3 rows in set (0.00 sec)

root@localhost [ngram]> 

mysql-boolean

MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier.
With this modifier, certain characters have special meaning at the beginning or
end of words in the search string. In the following query, the + and – operators indicate
that a word must be present or absent, respectively, for a match to occur.

In implementing this feature, MySQL uses what is sometimes referred to as implied Boolean logic, in which
+ stands for AND
– stands for NOT
[no operator] implies OR

参照: http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

root@localhost [ngram]> root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('きゅー' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
3 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('+きゅー -まいー' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
2 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('+きゅー -えー' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       12 | まいーえすきゅーえる           |
+----------+--------------------------------+
2 rows in set (0.00 sec)

root@localhost [ngram]> 
root@localhost [ngram]> SET GLOBAL innodb_ft_aux_table="ngram/N_DEMO";
Query OK, 0 rows affected (0.00 sec)

root@localhost [ngram]> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| いえ   |           15 |          15 |         1 |     15 |        3 |
| いー   |           14 |          14 |         1 |     14 |        3 |
| えす   |           14 |          14 |         1 |     14 |        9 |
| える   |           14 |          15 |         2 |     14 |       24 |
| える   |           14 |          15 |         2 |     15 |       24 |
| えー   |           15 |          15 |         1 |     15 |        6 |
| きゅ   |           14 |          15 |         2 |     14 |       15 |
| きゅ   |           14 |          15 |         2 |     15 |       15 |
| すき   |           14 |          15 |         2 |     14 |       12 |
| すき   |           14 |          15 |         2 |     15 |       12 |
| まい   |           14 |          15 |         2 |     14 |        0 |
| まい   |           14 |          15 |         2 |     15 |        0 |
| ゅー   |           14 |          15 |         2 |     14 |       18 |
| ゅー   |           14 |          15 |         2 |     15 |       18 |
| ーえ   |           14 |          15 |         2 |     14 |        6 |
| ーえ   |           14 |          15 |         2 |     14 |       15 |
| ーえ   |           14 |          15 |         2 |     15 |       21 |
| ーす   |           15 |          15 |         1 |     15 |        9 |
+--------+--------------+-------------+-----------+--------+----------+
18 rows in set (0.00 sec)

root@localhost [ngram]> 

filer2

参照: http://mysqlserverteam.com/innodb-full-text-n-gram-parser/


MySQL5.7から実装されるInnoDBの全文検索 in CJKモードの挙動について。
Natural Language Full-Text Searchsについて。

12.9.1 Natural Language Full-Text Searches
http://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html
By default or with the IN NATURAL LANGUAGE MODE modifier,
the MATCH() function performs a natural language search for a string against
a text collection. A collection is a set of one or more columns included in a FULLTEXT index.
The search string is given as the argument to AGAINST().

テストバージョン
version

検証用DB、テーブル、データの作成。
5.7のngramはDefaultのpluginになっているのでそのままインデックスまで作成して確認。

mysql>  CREATE DATABASE `ngram` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
Query OK, 1 row affected (0.00 sec)


mysql> CREATE TABLE N_DEMO
    -> (
    ->         FTS_N_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->         title VARCHAR(100),
    ->         FULLTEXT INDEX ngram_idx(title) WITH PARSER ngram
    -> ) Engine=InnoDB CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('mysql');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('MYSQL');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('MySQL');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('まいえすきゅーえる');
Query OK, 1 row affected (0.00 sec)

mysql> show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%ft_min%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| ft_min_word_len          | 4     |
| innodb_ft_min_token_size | 3     |
+--------------------------+-------+
2 rows in set (0.01 sec)

mysql> SET GLOBAL innodb_ft_aux_table="ngram/N_DEMO";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| my     |            2 |           4 |         3 |      2 |        0 |
| my     |            2 |           4 |         3 |      3 |        0 |
| my     |            2 |           4 |         3 |      4 |        0 |
| ql     |            2 |           4 |         3 |      2 |        3 |
| ql     |            2 |           4 |         3 |      3 |        3 |
| ql     |            2 |           4 |         3 |      4 |        3 |
| sq     |            2 |           4 |         3 |      2 |        2 |
| sq     |            2 |           4 |         3 |      3 |        2 |
| sq     |            2 |           4 |         3 |      4 |        2 |
| ys     |            2 |           4 |         3 |      2 |        1 |
| ys     |            2 |           4 |         3 |      3 |        1 |
| ys     |            2 |           4 |         3 |      4 |        1 |
| いえ   |            7 |           7 |         1 |      7 |        3 |
| えす   |            7 |           7 |         1 |      7 |        6 |
| える   |            7 |           7 |         1 |      7 |       21 |
| きゅ   |            7 |           7 |         1 |      7 |       12 |
| すき   |            7 |           7 |         1 |      7 |        9 |
| まい   |            7 |           7 |         1 |      7 |        0 |
| ゅー   |            7 |           7 |         1 |      7 |       15 |
| イエ   |            5 |           5 |         1 |      5 |        3 |
| エス   |            5 |           5 |         1 |      5 |        6 |
| エル   |            5 |           5 |         1 |      5 |       21 |
| キュ   |            5 |           5 |         1 |      5 |       12 |
| スキ   |            5 |           5 |         1 |      5 |        9 |
| マイ   |            5 |           5 |         1 |      5 |        0 |
| ュー   |            5 |           5 |         1 |      5 |       15 |
| ーえ   |            7 |           7 |         1 |      7 |       18 |
| ーエ   |            5 |           5 |         1 |      5 |       18 |
| ュー     |            6 |           6 |         1 |      6 |       15 |
| ーエ     |            6 |           6 |         1 |      6 |       18 |
| イエ     |            6 |           6 |         1 |      6 |        3 |
| エス     |            6 |           6 |         1 |      6 |        6 |
| エル     |            6 |           6 |         1 |      6 |       21 |
| キュ     |            6 |           6 |         1 |      6 |       12 |
| スキ     |            6 |           6 |         1 |      6 |        9 |
| マイ     |            6 |           6 |         1 |      6 |        0 |
+--------+--------------+-------------+-----------+--------+----------+
36 rows in set (0.00 sec)

mysql> 

実際に全文検索(ngram)の動作を確認してみる。

mysql> select * from N_DEMO;
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        1 | mysql                       |
|        2 | MYSQL                       |
|        3 | MySQL                       |
|        4 | マイエスキューエル          |
|        5 | マイエスキューエル                   |
|        6 | まいえすきゅーえる          |
+----------+-----------------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('my' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

mysql> SELECT FTS_N_ID,MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE) AS score FROM N_DEMO;
+----------+--------------------+
| FTS_N_ID | score              |
+----------+--------------------+
|        1 | 0.1812381148338318 |
|        2 | 0.1812381148338318 |
|        3 | 0.1812381148338318 |
|        4 |                  0 |
|        5 |                  0 |
|        6 |                  0 |
+----------+--------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('マイ' IN NATURAL LANGUAGE MODE);
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        5 | マイエスキューエル                   |
+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('マイ' IN NATURAL LANGUAGE MODE);
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        4 | マイエスキューエル          |
+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('まい' IN NATURAL LANGUAGE MODE);
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        6 | まいえすきゅーえる          |
+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> 

NGRAM

ngram_token_sizeがDefaultで2になっている時の挙動
2文字で設定されているので、検索結果も検索対象と同じ2文字を含むデータが検出されいる。
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_ngram_token_size

ngram-token-def

mysql> INSERT INTO N_DEMO (title) VALUES ('まい'), ('えす'), ('sq'), ('sl'), ('ql');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from N_DEMO;
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        1 | mysql                       |
|        2 | MYSQL                       |
|        3 | MySQL                       |
|        4 | マイエスキューエル          |
|        5 | マイエスキューエル                   |
|        6 | まいえすきゅーえる          |
|        7 | まい                        |
|        8 | えす                        |
|        9 | sq                          |
|       10 | sl                          |
|       11 | ql                          |
+----------+-----------------------------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
|        9 | sq    |
|       11 | ql    |
+----------+-------+
5 rows in set (0.00 sec)

mysql> SELECT FTS_N_ID,MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE) AS score FROM N_DEMO;
+----------+---------------------+
| FTS_N_ID | score               |
+----------+---------------------+
|        1 | 0.38602644205093384 |
|        2 | 0.38602644205093384 |
|        3 | 0.38602644205093384 |
|        4 |                   0 |
|        5 |                   0 |
|        6 |                   0 |
|        7 |                   0 |
|        8 |                   0 |
|        9 | 0.19301322102546692 |
|       10 |                   0 |
|       11 | 0.19301322102546692 |
+----------+---------------------+
11 rows in set (0.01 sec)

mysql> 

ngram_token_sizeを2から3へ変更して再度検索した結果
– この値の変更には、SQLインスタンスの再起動が必要です。
– インデックスの再作成も必要になります。
先程と同じ検索をしても、結果が異なる事が確認出来る。

root@localhost [ngram]> system cat /etc/my.cnf | grep ngram_token_size
ngram_token_size=3
root@localhost [ngram]> show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 3     |
+------------------+-------+
1 row in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)

root@localhost [ngram]> alter table N_DEMO drop index ngram_idx;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [ngram]> alter table N_DEMO ADD FULLTEXT INDEX ngram_idx (title) WITH PARSER ngram;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> SELECT FTS_N_ID,MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE) AS score FROM N_DEMO;
+----------+--------------------+
| FTS_N_ID | score              |
+----------+--------------------+
|        1 | 0.3184022605419159 |
|        2 | 0.3184022605419159 |
|        3 | 0.3184022605419159 |
|        4 |                  0 |
|        5 |                  0 |
|        6 |                  0 |
|        7 |                  0 |
|        8 |                  0 |
|        9 |                  0 |
|       10 |                  0 |
|       11 |                  0 |
+----------+--------------------+
11 rows in set (0.00 sec)

root@localhost [ngram]> 

alter_table