以前の記事でMySQLの全文検索に関して書きましたが、では実際にlikeを用いた検索と比べてパフォーマンスにどの程度違いが出るのか、検証してみました。
前提
データ
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検索よりもパフォーマンスは良くなると思われます。
ただし形態素解析でも「メロンは美味しい」のようなキーワード検索は、キーワードが品詞に分解されるため結局フレーズ検索が必要となります。

- 作者: 高村大也,奥村学
- 出版社/メーカー: コロナ社
- 発売日: 2010/07/01
- メディア: 単行本
- 購入: 13人 クリック: 235回
- この商品を含むブログ (42件) を見る

現場で使える MySQL (DB Magazine SELECTION)
- 作者: 松信嘉範
- 出版社/メーカー: 翔泳社
- 発売日: 2006/03/17
- メディア: 単行本
- 購入: 9人 クリック: 103回
- この商品を含むブログ (45件) を見る

- 作者: 遠藤俊裕,坂井恵,館山聖司,鶴長鎮一,とみたまさひろ,班石悦夫,松信嘉範
- 出版社/メーカー: 翔泳社
- 発売日: 2011/08/26
- メディア: 大型本
- 購入: 9人 クリック: 82回
- この商品を含むブログ (9件) を見る