MySQLで全文検索(フルテキストインデックス)を使用する方法

普段利用しているサイトに検索用のテキストボックスがあり、そこに何らかのワードを入れて検索するとあいまい検索になるもののLIKE検索とはどうも挙動が違うように見受けられる。調べたところどうも全文検索(フルテキストインデックス)というものっぽい。以下に全文検索(フルテキストインデックス)の利用方法をメモ。

準備

使用するデータベース

以下構造のデータベースを使用する。

mysql> SHOW COLUMNS FROM `test_table`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| text  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

フルテキストインデックスの作成

基本構文は以下の通り。

CREATE FULLTEXT INDEX `index` 
ON `テーブル名` (`カラム名`) 
WITH PARSER ngram;

今回はtest_tableのtextカラムに対してインデックスを作成したいため以下SQL文になる。

CREATE FULLTEXT INDEX `index` 
ON `test_table` (`text`) 
WITH PARSER ngram;

データの用意

以前にメモしたFakerPHPのrealTextで文章を作成し、textカラムに登録した。

検索方法

普通の全文検索

SELECT * 
FROM test_table 
WHERE MATCH (`text`) AGAINST ('とうとう' IN NATURAL LANGUAGE MODE);

上記SQL文を実行すると以下結果が返ってきた。

+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | text                                                                                                                                                   |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|  3 | けん命めいにげてにげたけど、とうとうすっかり覚悟かくごして、この人たちをだいて、それからぼくたちの。                                                   |
|  9 | にうかんでいるのです。それでもとうとうこんなになってはねあげられたねえ。僕ぼくこんな愉快ゆかいにな。                                                   |
| 13 | えるもので、太陽たいようと同じようなけだものです」カムパネルラがまた何気なくしかるように叫さけびま。                                                   |
| 17 | わたしたちはこんないいとこで僕ぼくはどうしてぼくがなんにもしないのにあんなことを言いうときはきのど。                                                   |
| 70 | 計屋とけいやの店には明るくネオン燈とうがついて、ほんとうの幸福こうふくをさがすぞ」ジョバンニは、さ。                                                   |
|  2 | はがらんとなってしまった。ぼく、水筒すいとうを忘わすれてきた。けれどもだんだん気をつけて見ると、さ。                                                   |
| 38 | ぼろぼろの外套がいとうを着きて青年の腕うでにすがって不思議ふしぎそうに立ちどまって、すばやく船から。                                                   |
| 69 | な姿勢しせいのまままた水の中へくぐって行くのでした。「今晩こんばんはありがとう、おっかさんが、ほん。                                                   |
| 71 | ってそれを受うけ取とってかすかにうなずきました。「とうもろこしの林になってしまったから」カムパネル。                                                   |
| 72 | て燈台看守とうだいかんしゅがやっと少しわかったようにぼんやり白い柱はしらとが、ちらっとこっちを見て。                                                   |
| 74 | り、頬ほおにはつめたい涙なみだがうかんでいるのでした。そしてほんとうに鷺さぎだねえ」二人ふたりは一。                                                   |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+

idの3 / 9以外に「とうとう」というワードは入っていないもののヒットしている点が確認できる。

スコア付きでの検索方法

SELECT *, MATCH (`text`) AGAINST ('とうとう' IN NATURAL LANGUAGE MODE) AS score 
FROM test_table 
ORDER BY score desc;

上記SQL文を実行すると以下結果が返ってきた(scoreが0のものは省略)。

+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| id | text                                                                                                                                                   | score              |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
|  3 | けん命めいにげてにげたけど、とうとうすっかり覚悟かくごして、この人たちをだいて、それからぼくたちの。                                                   |  2.417814016342163 |
|  9 | にうかんでいるのです。それでもとうとうこんなになってはねあげられたねえ。僕ぼくこんな愉快ゆかいにな。                                                   |  2.417814016342163 |
| 17 | わたしたちはこんないいとこで僕ぼくはどうしてぼくがなんにもしないのにあんなことを言いうときはきのど。                                                   | 1.6352107524871826 |
| 13 | えるもので、太陽たいようと同じようなけだものです」カムパネルラがまた何気なくしかるように叫さけびま。                                                   | 1.6352107524871826 |
| 70 | 計屋とけいやの店には明るくネオン燈とうがついて、ほんとうの幸福こうふくをさがすぞ」ジョバンニは、さ。                                                   | 0.7826033234596252 |
|  2 | はがらんとなってしまった。ぼく、水筒すいとうを忘わすれてきた。けれどもだんだん気をつけて見ると、さ。                                                   | 0.3913016617298126 |
| 38 | ぼろぼろの外套がいとうを着きて青年の腕うでにすがって不思議ふしぎそうに立ちどまって、すばやく船から。                                                   | 0.3913016617298126 |
| 69 | な姿勢しせいのまままた水の中へくぐって行くのでした。「今晩こんばんはありがとう、おっかさんが、ほん。                                                   | 0.3913016617298126 |
| 71 | ってそれを受うけ取とってかすかにうなずきました。「とうもろこしの林になってしまったから」カムパネル。                                                   | 0.3913016617298126 |
| 74 | り、頬ほおにはつめたい涙なみだがうかんでいるのでした。そしてほんとうに鷺さぎだねえ」二人ふたりは一。                                                   | 0.3913016617298126 |
| 72 | て燈台看守とうだいかんしゅがやっと少しわかったようにぼんやり白い柱はしらとが、ちらっとこっちを見て。                                                   | 0.3913016617298126 |

スコアが〇以上での検索方法

SELECT *, MATCH (`text`) AGAINST ('とうとう' IN NATURAL LANGUAGE MODE) AS score 
FROM test_table 
WHERE MATCH (`text`) AGAINST ('とうとう' IN NATURAL LANGUAGE MODE) > 0.5
ORDER BY score desc;

上記SQL文を実行すると以下結果が返ってきた。

+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| id | text                                                                                                                                                   | score              |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
|  3 | けん命めいにげてにげたけど、とうとうすっかり覚悟かくごして、この人たちをだいて、それからぼくたちの。                                                   |  2.417814016342163 |
|  9 | にうかんでいるのです。それでもとうとうこんなになってはねあげられたねえ。僕ぼくこんな愉快ゆかいにな。                                                   |  2.417814016342163 |
| 13 | えるもので、太陽たいようと同じようなけだものです」カムパネルラがまた何気なくしかるように叫さけびま。                                                   | 1.6352107524871826 |
| 17 | わたしたちはこんないいとこで僕ぼくはどうしてぼくがなんにもしないのにあんなことを言いうときはきのど。                                                   | 1.6352107524871826 |
| 70 | 計屋とけいやの店には明るくネオン燈とうがついて、ほんとうの幸福こうふくをさがすぞ」ジョバンニは、さ。                                                   | 0.7826033234596252 |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+

所感

こちらのサイトによるとLIKE検索よりも実行速度が速いらしい。場合によってはLIKE検索より全文検索の方が良いかも。

参考サイト

https://zenn.dev/hiroakey/articles/9f68ad249af20c