MySQLにおけるlike検索と全文検索のパフォーマンス比較

以前の記事でMySQLの全文検索に関して書きましたが、では実際にlikeを用いた検索と比べてパフォーマンスにどの程度違いが出るのか、検証してみました。

前提

環境

Mac OS X、MySQL5.7、ストレージエンジンにInnoDBを使います。

データ

or検索用とngramによる全文検索用のテーブルを用意します。

mysql> desc fruits_for_or;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | YES  |     | NULL    |                |
| desctext | text         | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

mysql> desc fruits_for_ngram;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | YES  |     | NULL    |                |
| desctext | text         | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

nameにはフルーツ名、desctextにwikipediaの説明文を下記のとおり格納します。なおfruits_for_ngramにはngram用に分割した文字列を格納してfulltext indexを作ります。

メロン（和名：メロン、英名：melon、学名：Cucumis melo）は、果実を食用にするウリ科の一年生草本植物である。また、その果実のこと。

スイカ（西瓜、学名: Citrullus lanatus）は、果実を食用にするために栽培されるウリ科のつる性一年草。また、その果実のこと。

バナナ（甘蕉、実芭蕉、学名 Musa spp.）はバショウ科バショウ属のうち、果実を食用とする品種群の総称。また、その果実のこと。いくつかの原種から育種された多年草。種によっては熟すまでは毒を持つものもある。

パイナップル（パインアップル、パインナップル、英：pineapple、学名：Ananas comosus）は、熱帯アメリカ原産のパイナップル科の多年草。単にパインと略して呼ばれることもあるほか、昔は鳳梨とも呼ばれていた。また、果実だけをパイナップルと呼び、植物としてはアナナスと呼ぶこともある。

検証

4,000件

まずは1,000件 ✕ 4=4,000件のデータを対象に検索してみます。

/* like検索 */
mysql> select count(*) from fruits_for_or where desctext like ('%メロン%');
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.02 sec)

/* 全文検索 */
mysql> select count(*) from fruits_for_ngram where match(desctext) against('メロ ロン');
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)

0.01秒差。ほとんど変わりないです。

40,000件

次は10倍して、10,000件✕4=40,000件で検証してみます。

/* like検索 */
select count(*) from fruits_for_or where desctext like ('%メロン%');
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.16 sec)

/* 全文検索 */
mysql> select count(*) from fruits_for_ngram where match(desctext) against('メロ ロン');
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.04 sec)

0.1秒以上の差が出ました。全文検索の時間はlike検索の1/4程度です。

400,000件

最後は更に10倍の100,000件✕4=400,000件で検証します。

/* like検索 */
mysql> select count(*) from fruits_for_or where desctext like ('%メロン%');                     
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (1.30 sec)

/* 全文検索 */
mysql> select count(*) from fruits_for_ngram where match(desctext) against('メロ ロン');
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.35 sec)

やっぱり全文検索は早い！と言いたいところですが

実は上記の全文検索 SQL、不備があります。
それはこのままのSQLだと「メロ」"または"「ロン」を含む文章を抽出してくるため。
例えば「"メロ"ディー」なんかも結果として得られてしまします。

正しく「メロン」を抽出するのであれば、前回の記事で書いたとおりフレーズ検索を使います。

mysql> select count(*) from fruits_for_ngram where match(desctext) against('"メロ ロン"');
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (12.25 sec)

お、遅い！

何故こんなに遅いのか？
それは次回まとめたいと思います。

※上記はあくまでNgramを利用したことでフレーズ検索が必要となっているため、これだけ秒数がかかっています。
形態素解析の場合はフレーズ検索なしに「メロン」で検索できるためlike検索よりもパフォーマンスは良くなると思われます。
ただし形態素解析でも「メロンは美味しい」のようなキーワード検索は、キーワードが品詞に分解されるため結局フレーズ検索が必要となります。