人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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が悪いようなのです。
調べてみたのですが、該当する情報がなく困っています。原因や対策を知っている方がいましたら、教えてください。


●質問者: selter
●カテゴリ:ウェブ制作
✍キーワード:MySQL ON SELECT キー クエリ
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● pahoo
●23ポイント

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


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


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

◎質問者からの返答

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

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


2 ● chuken_kenkou
●23ポイント

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

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

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

ORDER BY vote_table.vote DESC

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


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


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

◎質問者からの返答

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

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

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


3 ● shintabo
●22ポイント

# 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しないとアレですけど。。。

◎質問者からの返答

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

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


4 ● chuken_kenkou
●22ポイント

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

create index vote_idx on vote_table(vote)

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

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



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

◎質問者からの返答

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

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ