MYSQLチューニング初心者です。

質問

MYSQLチューニング初心者です。
MYSQLでEXPLAINでチューニングをしている際以下の壁にぶち当たって打開方法がないか模索しております。
テーブル構造はダミーですがたとえばa1,a2,a3というテーブル下記記載
SELECT * from a1 left join a2 on a1.id = a2.id left join a3 on a3.id = a1.mailbox_id \G

a1.id: INT 主キー
a1.mailbox_id: INT INDEX
a1.title: varchar(50)
a1.body: text
a1.created_at: datetime

a2.id: INT 主キー
a2.mail_address: varchar(50) INDEX

a3.id: INT 主キー
a3.priority: INT
a3.mail_box_name: varchar(50)

上記テーブル構造にて（EXPLAIN SELECT a1.id as id,a1.title,a1.body,a1.created_at,a2.mail_address,a3.mail_box_name FROM a1 left join a2 on a1.id = a2.id left join a3 on a3.id = a1.mailbox_id WHERE a2.mail_address = 'hogehoge@test.jp' order by created_at DESC;）を行うと、 Using temporary; Using filesort；が必ず出てきて対処方法に詰まっています。
order句のcreated_atをINDEXにすればよいのかな？と思いつけてみたのですが、a1.idのプライマリキーが優先され、結局同じ症状が出ます。

マルチインデックスでa1.id,created_atを付加し、FORCE INDEXをしたときはUsing temporary; Using filesortは消えますが、ExplainのrefがNULLになってしまいます。

方向性としてはtemporaryを使わない、indexでのsortを可能にする方法です。
まとまっていない文章で申し訳ございません。どうぞご教授のほどよろしくお願いします。

＞＞データ量としてはa1・a2は50万レコード　a3は10件ほどです。
＞＞mysql Ver 14.14 Distrib 5.1.40, for redhat-linux-gnu (i686) using readline 5.1

回答No.1

yambejp

考え方だけ。

複数テーブルを結合した結果をソートしているんだから
テンポラリが発生するのは当然。

そもそもorder by する必要があるのかどうか疑問？
ページングしたいならorderbyではない方法でランク付けして、
範囲を指定して処理すればよいでしょうし。

もしくは参照頻度が高くデータ更新が必ずしも高くない処理なら
結果を通常テーブルをつくっておいてそれにバルクで流し込みをして、
ソート用フィールドにindexを貼っておくとよいです。
ユーザーはデータ更新を任意のタイミングですることで、
完全なリアルタイムではないですが、非常に高速な仕組みになります。

補足

yambejpさん、ご回答ありがとうございます。

＞＞そもそもorder by する必要があるのかどうか疑問？

order_byをかけている理由ですが、最新のデータからどうしても5件取り出し、アプリケーション側に表示させたいため行っております。idと日付の順序も現在DB上ではそろっていないため、こちらも悩ましく思ってます。

＞＞もしくは参照頻度が高くデータ更新が必ずしも高くない処理なら結果を通常テーブルをつくっておいてそれにバルクで流し込みをして、ソート用フィールドにindexを貼っておくとよいです。

更新用テーブルから参照用テーブル（結合されたDBデータを格納）してwhere ～ odrderby～で解決するということですね。それなら単一テーブルで高速化が実現できそうです。

ただ今回既にに使用されているアプリケーションとなっているため、DBのチューニングでどうにか出来れば！！と思っておりました。

JOINをした場合にはorder byではINDEXを付けたとしてもUsing temporaryは発生するんですね。

今のDBそのままの構造で解決できるのはwhere句指定のカラムにINDEXを付けることだけが最良なのでしょうか。