「MySQL LIKE 性能」あたりでググると、「LIKEを使った部分一致検索は比較的遅いから、LIKEを使わなくていいなら使わない方がいい」なんていう記事が目立つので、それならばどのぐらい遅いのか、実際に検証してみました。
……そう、今思えばなぜあんなことをしてしまったのか。そしてなぜこんなことになってしまったのか。未だによくわかりません。
検証にあたって、ちょうど手元にあったので使用してみた環境をうｐ。
- CentOS 5.7
- apache 2.2.3
- MySQL 5.0.95
- PHP 5.3.3
- phpmyadmin 3.4.9
検証方法は、phpmyadminの「SQL」タブから、試験対象のSQL文をそれぞれ5回ずつ実行し、1番早いタイムと遅いタイムを記録します。
試験対象は以下の通り。
- インデックスありのカラムに対して完全一致検索(
field= “value”) - インデックスありのカラムに対して部分一致検索(
fieldLIKE “%value%”) - インデックスなしのカラムに対して完全一致検索(
field= “value”) - インデックスなしのカラムに対して部分一致検索(
fieldLIKE “%value%”) - インデックスなしのカラムに対して前方一致検索(
fieldLIKE “value%”) - インデックスなしのカラムに対して後方一致検索(
fieldLIKE “%value”)
検証に用いたテーブルには、2525行のデータが含まれていて、インデックスありのカラムの型はint(16)、インデックス無しのカラムの型はvarchar(255)です。
（追記） このテーブルのエンジンはInnoDB、文字コードはutf8_general_ci、サイズは528.0KBです。書き忘れてた(ﾉ∀)ﾀﾊｰ
以下が検証結果。
- 最速：0.0005秒 / 最遅：0.0006秒
- 最速：0.0008秒 / 最遅：0.0008秒
- 最速：0.0059秒 / 最遅：0.0066秒
- 最速：0.0041秒 / 最遅：0.0045秒
- 最速：0.0006秒 / 最遅：0.0006秒
- 最速：0.0006秒 / 最遅：0.0007秒
ええ、なんというか……LIKEのほうが早いです（ｱｾｱｾ）。決して取り違えているわけではありません。何を間違ったらこうなるのか……でも確かにLIKEのほうが早いんですよね、この結果を見る限りでは。
検証に用いたカラムは商品名が入っているものでして、テーブルの中では比較的長い文字列が入っているんですよね。もしかしたらそれが影響しているのかもしれません。
しかし、SQLでAVG(CHAR_LENGTH(field`)) を走らせてみたら、平均文字数は22.9770でしたので、恐ろしく長い文字数が入っている、というわけではありません。多分。
一応、文字数と検索速度の関連を確かめるため、試しに、以下の追加検証を 行なってみました。
- 文字数の比較的少ない、インデックスのないカラムに対して完全一致検索
- 文字数の比較的少ない、インデックスのないカラムに対して部分一致検索
このとき検証に使っているフィールドの平均文字数は、9.2222です。半分ぐらいの文字数になっていますね。
そして、検証の結果は以下の通りになりました。
- 最速：0.0057秒 / 最遅：0.0075秒
- 最速：0.0061秒 / 最遅：0.0064秒
今回は差が縮まりました。しかし、LIKE検索のタイムが遅くなったことによって縮まっているだけなので、文字数と速度の間の関連性はよく分かりません（ぁ
まだ気に入らないので、今度はLIKE検索に使う検索文字列の文字数を増やしたり減らしたりしてみましたが、増やしてもそう変わらないし、減らしたらかなり早くなっちゃったし（！？）、どういうふうにやればいいのかさっぱり分かりません（；ω；｀）
とりあえず今回の結論は、部分一致検索を使わざるをえない場面では……どんどん使え！ ですね。不本意ながらorz
ぶらりコメント失礼します
最初の1～6では1が最速になる理由は明白なんだけど、
3と5でそんな変わる理由が分からないですね。
Oracle脳な私だと２と４の違いもわからんですが:(；ﾞﾟ’ωﾟ’):
前方と後方で時間が変わらない所をみると
曖昧検索に最適化された探索ってことなんですかねぇ
MySQLの中身が気になるとこですね。
@めるか:
コメントどうもですm(__)m 多分mixi経由だよね|-`)
Oracleとか一度も触ったことないどうしよう（；ω；｀）
まぁそれはいいとして、自分で示しておいて言うのもあれだけど、謎がそこかしこに散りばめられた結果ですなこれは；；
2と4は確かになんか変な感じがするｗ LIKE検索にインデックスは有効なのか！？とか思っちゃいますな……
テーブル自体は特に何か特別なカラムやら文字列やらがあるわけじゃないんだけどなぁ。
なぜこうなるのか、理由の見当が全くつかないほど不勉強なので、チューニングに関する本を読んできてからなんか分かったら別の記事で書きたいと思います。
前ゲームでブログの事聞いてたけどさっぱりみてなかったんだぜ☆
つぶやきに面白そうなネタあったからのってみたのですｗ
2500件程度なら何をやっても差はつかないと思います。
実際のプロジェクトでもテストでは問題ないけど本番で
何十万件と件数が多い状態になって初めてパフォーマンスに
問題があるのがわかるケースが多いです。
データが全く増えないシステムならいいですが、
件数が増える可能性があるなら気をつけた方がいいです。