MySQLのLIKEが遅い

検索ボックスから入力すると WHERE column_name LIKE "%foo%" というような部分一致検索する機能があり、この処理が非常に遅い。
% で始まる LIKE はインデックスを使えないから。

データベースが MySQL 5.1(InnoDB) の場合に、これをはやくするアプローチを考えてみる

MySQL の設定を見直す

設定がデフォルトのままだったら、チューニングする。

ベースとなる設定 http://blog.nomadscafe.jp/2012/10/mysql-mycnf-github.html

SQL を見直す

explain実行計画を確認LIKE 以外の箇所で改善点がないか見直す。

今回のケースでは、サブクエリーがフルスキャンしていたので、インデックスを追加して3000倍達成。

前方一致検索にする

インデックスが使えるように、部分一致検索(LIKE "%foo%")はやめて前方一致検索(LIKE "foo%")にする

完全一致や前方一致で十分なのに、無意味に部分一致になっている箇所があれば完全/前方一致にする。
要件によってはどうしても部分一致が必要ということもあるので、ケースバイケース

フルテキスト検索を利用

MySQL の Full-Text Search を利用する
残念なことに、この機能はストレージエンジン MyISAM に対して提供されてきた機能で、InnoDB は 5.6 以降でしか利用できない。
更に残念なことに、パーサーは欧米言語仕様なので、分かち書きをしてあげないとまともに使えない

The FULLTEXT parser determines where words start and end by looking for certain delimiter characters; for example, “ ” (space), “,” (comma), and “.” (period). If words are not separated by delimiters (as in, for example, Chinese), the FULLTEXT parser cannot determine where a word begins or ends. To be able to add words or other indexed terms in such languages to a FULLTEXT index, you must preprocess them so that they are separated by some arbitrary delimiter such as “””.
http://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html

日本語検索したい場合は次の URL を参照
http://y-ken.hatenablog.com/entry/mysql-casual-talks-vol4-innodb-fts

5.1 から 5.6 へのバージョナップはすぐにはできないので今回は見送り。

他のサーチエンジンを利用

groonga/sphinx/solr など他のサーチエンジンを利用する。

検索は重要な機能ではなく、オーバースペックということで今回は見送り。

References

  • High Performance MySQL, 3rd Edition
    Ch.7 : Advanced MySQL Features – Full-Text Searching (pp. 305-)
Advertisements
Tagged with:
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
  • RT @__apf__: How to write a research paper: a guide for software engineers & practitioners. docs.google.com/presentation/d… /cc @inwyrd 6 months ago
  • RT @HayatoChiba: 昔、自然と対話しながら数学に打ち込んだら何かを悟れるのではと思いたち、専門書1つだけ持ってパワースポットで名高い奈良の山奥に1週間籠ったことがある。しかし泊まった民宿にドカベンが全巻揃っていたため、水島新司と対話しただけで1週間過ぎた。 それ… 6 months ago
  • RT @googlecloud: Ever wonder what underwater fiber optic internet cables look like? Look no further than this deep dive w/ @NatAndLo: https… 6 months ago
  • @ijin UTC+01:00 な時間帯で生活しています、、、 1 year ago
  • RT @mattcutts: Google's world-class Site Reliability Engineering team wrote a new book: amazon.com/Site-Reliabili… It's about managing produc… 1 year ago
%d bloggers like this: