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

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

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

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

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をソートするといった方法は考えられないか？

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

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

create index vote_idx on vote_table(vote)

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

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

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