MySQLでデータベースを構築しています。例えば


item_table
vote_table

というふたつのテーブルがあります。item_tableは商品を格納し、vote_tableはその商品の評価を入れています。評価のない商品は、vote_tableにはデータがありません。

これを、評価順に並べてデータを取り出したいので

SELECT * FROM item_table LEFT JOIN vote_table ON item_table.item_id=vote_table.item_id ORDER BY vote_table.vote DESC;

としました。

しかし、このクエリがものすごく時間のかかる処理になってしまっています。item_tableに 10,000件弱のデータで、主キーの設定なども正しく行えていますが、vote_tableに 2件データを入れただけでも、10秒近くかかってしまいます。

ちなみに「ORDER BY」以降を削除すると、0.000xxx秒といった速度で検索できるので、どうも order byが悪いようなのです。
調べてみたのですが、該当する情報がなく困っています。原因や対策を知っている方がいましたら、教えてください。

回答の条件
  • 1人2回まで
  • 登録:2009/02/18 00:15:51
  • 終了:2009/02/22 13:07:26

回答(4件)

id:pahoo No.1

pahoo回答回数5960ベストアンサー獲得回数6332009/02/18 10:55:05

ポイント23pt

ご質問の要件ですと、vote_table は item_table の部分集合になっているのですよね。


となると、vote_table.vote にインデックスが張ってあったとしても、その join 構造では意味をなさなくなります。つまり、余計なソートが発生していると思われます。


条件は少し違うのですが、「UNIQE INDEXと JOIN とORDER BY で無用なソートが発生するケースとその回避方法」の末尾にある考察が参考になるでしょう。

id:selter

回答ありがとうございます。

なるほど、やはり SQLを根本から見直さないとダメそうですね。ありがとうございました。

2009/02/19 12:39:17
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542009/02/18 17:25:19

ポイント23pt

MySQLの質問をする場合は、バージョンを明記してください。

  • MySQL 4.0以前・・・一部のジョイン、union、一時表などの実装
  • MySQL 4.1・・・ジョインの拡張、サブクエリ、unicodeなどの実装、一部の仕様変更
  • MySQL 5.0・・・ビュー、トリガ、ストアドプロシジャなどの実装

と、すぐに思いつくだけでも大きな機能拡張、一部の仕様変更があります。また、仕様改善なども行われています。

今回のSQLでも問題は、すでにお気づきのように、

ORDER BY vote_table.vote DESC

でインデクスを活用できず、作業ファイルを用いたソートになっているからです。しかも、LEFT JOINでitem_tableの行数分が対象になってしまいます。


これを、評価順に並べてデータを取り出したい


対策としては、「全データを対象に評価順」というのを、すべてSQLでやろうとするには無理があります。

  • データが1万件程度であれば、MySQLの作業メモリのサイズを大きくすることで、ある程度は性能改善できるかも知れない
  • INNER JOINで、voteが存在しないものは表示しないという仕様ではだめなのか
  • voteが存在しないものもどうしても表示したいなら、inner joinでvoteのソートするものと、しないもののクエリを分けられないか
  • itemのグループなどを設け、その中でvoteをソートするといった方法は考えられないか?
id:selter

MySQLのバージョンは、4.1になります。

ご提案いただいた方法では、3つめのクエリを分けるという方法は可能なのですが、プログラムを大きく作り替えなければならず、できれば SQLで解決できたら良いなと思いました。

しかし、やはり難しそうですね。。もう少し試行錯誤してみます。ありがとうございました。

2009/02/19 12:40:24
id:shintabo No.3

shintabo回答回数45ベストアンサー獲得回数102009/02/21 13:47:07

ポイント22pt

# SQLでの問題、解決などは、皆様が書いているので、少々違う方法で。

# item_idがvote_tableに存在しないかもしれない、ということは、テーブルを新規追加するイメージでしょうか。

mysql> INSERT INTO vote_table (item_id, vote) SELECT item_id, 0 FROM item_table;

# をやってしまって、LEFT JOINをやめてしまうのはどうですか?

# もちろん、item_idをINSERTする時に、vote_tableもINSERTしないとアレですけど。。。

id:selter

回答ありがとうございます! それ、いい方法ですね。スマートではないですが、なんとか現状を打破するという意味では、ぜんぜんありな方法です。

実際にやるかどうかは、ちょっと考えないと行けないですが・・非常に参考にありました。ありがとうございました!

2009/02/21 15:01:49
id:chuken_kenkou No.4

chuken_kenkou回答回数722ベストアンサー獲得回数542009/02/21 16:10:49

ポイント22pt

vote_tableに集約できるのであれば、

create index vote_idx on vote_table(vote)

というインデクスを定義すると、order byの背景で行われる「作業メモリや作業ファイルを用いてのソート」を抑止できるかも知れません。

実際に抑止できるかどうかは、EXPLAINで実行計画がどのようになるかを確認してみてください。



MySQL :: MySQL 4.1 リファレンスマニュアル :: 5.2 SELECT ステートメントおよびその他のクエリの最適化

id:selter

インデックスをしっかり定義してあげれば、解決できるのですか。それだと、非常に助かります。

試してみて、調べてみますね。ありがとうございました!

2009/02/22 13:06:32
  • id:standard_one
    去年だか一昨年だか、JOINしないでプログラム側で処理した方が速いっていう結果がでてましたよね。
    まぁJOIN使っちゃイカンというワケじゃないですけど。
  • id:selter
    コメントありがとうございます。JOINは結構くせ者の SQLなんですかね・・
    見直してみます。ありがとうございます。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません