「全てのカテゴリから検索」のような絞り込みの少ないSQLの負荷について

Question

MySQL/PHP/CakePHPで開発しています。
30万レコードを超えるデータベースを扱っているのですが、
「全てのカテゴリから検索」のように絞り込みがあまりなく、該当するレコード件数がかなりの数になる場合、
どのようにしてSQLの負荷を下げれば良いでしょうか？
実行に非常に時間がかかります。(3秒以上)

「全てのカテゴリ」を指定する場合、指定しているWHEREの条件も、カーディナリの低いものばかりで、
例えばis_publishカラムで0=表示中/1=非表示中のようなもので、
is_publish = 0と指定するくらいの条件が複数あるのみです。

EXPLAINでSQLを調べたところ、インデックスは使用されていますが、
rowsが15万以上ありました。
(ExtraはUsing index condition; Using where; Using temporary)

他の複数のテーブルとアソシエーションしており、LEFT JOINやINNNER JOINも含んでいます。
また、検索で使用しているため、%検索ワード%のLIKE検索を併用しています。
(FULLTEXTインデックスを使用した全文検索に切り替えてみましたが余計に遅くなりました。)

見直すところは多々あると思いますが、自分ではどうにも解決策が見つからないため、
何かご教授頂けますと幸いです。

宜しくお願い致します。

Answer 1

nginxをキャッシュサーバとして介在させるといった手段があります。
特に画像を検索結果として出力する際には、nginx側で一定期間画像をキャッシュできるので効果を発揮します。

また、発行されるクエリをDebugkitで確認してください。select * from hoge where〜みたいになってると、重くなることが多いですので、一個一個フィールドをきちんと書きましょう。

あとはmy.cnfの設定やapacheの設定を見なおしてみてください。apache2.2とかだとkeepaliveがOnになってると、プロセスを一個一個処理するのでタイムアウトするまで待たされることがあります。apacheのベンチマークテストはabコマンドで実施できますので、それはググってください。

スロークエリの検出についてmy.cnfできちんと時間を設定し、遅いクエリを切り分けましょう。それでもダメならサーバのスペックを上げるのも効果的です。

Answer 2

全文検索も候補に入れた方が良さそうな感じですね。
MySQLで全文検索をするために必要なこと

Answer 3

スピードを優先するなら

%検索ワード%のLIKE検索を併用

は、使うべきではないですね。
所定のカラムにたいしてキーワードを利用して検索するのであれば
全文検索か分かち書きした上で一つ一つを個別に登録するのが妥当です。
検索方法は完全一致に絞るべきです
あとはテーブルの設計と、どういった検索を希望するかによるでしょう。

Answer 4

言及がないので確認の色が強いのですが、ORDER BYとLIMITは使用されていますか？

もし検索結果の全件が必要ない状況であれば、数量を絞ることで改善する可能性があります。
順序の基準によっては、インデックスの追加も必要になるかもしれません。