mysql インデックスの張り方。

下記のようなSQLがある場合、どのようにインデックスを張れば抽出が早くなるか教えて頂けますでしょうか？
テーブルBにカラム1、カラム2と複合でインデックスを張りましたがあまり早くなりません(Durationが1secぐらい早くなった)でした。
※作業はMySQLWorkbenchを使用。

select dd.*
from A o
inner join (
select *
from B
where カラム1 = 1
and カラム2 = 16
) dd on o.カラム3 = dd.カラム3
where o.カラム4 >= DATE_ADD(NOW(),INTERVAL -3 MONTH)
group by dd.カラム5
order by count(dd.カラム5) desc;

最後まで読んでいただきありがとうございます。
ご回答宜しくお願いします。

追記
出来ればインデックスのみで作業出来ればと思っていましたが
皆様から頂いたご回答から、SQL修正も視野に考えております。
すみませんSQLに関して未熟でして、上記の場合、SQLを編集し早くするアドバイスを頂けますでしょうか？

ぎゃー!時間がありません。だれか助けてー!!!

行動規範の内容に同意します

回答3件

Aテーブルのカラム3とカラム4の2つを複合INDEXしてみては？Bテーブルはindex2:(質問で設定した、カラム1、カラム2)のままで。

でも、他の方も言われている通り、JOINは遅くする一因ですので
必要ないのであれば組み替えるのが手っ取り早い気がします。

投稿2015/08/26 10:42

icham

総合スコア559

spring

2015/08/27 02:08

ご回答ありがとうございます。 >Aテーブルのカラム3とカラム4の2つを複合INDEXしてみては？Bテーブルはindex2:(質問で設定した、カラム1、カラム2)のままで。承知しました。下記の通り設定しEXPLAINした結果を記載します。 ●Aテーブル PK:カラム3 index1:(仮のやつ) index2:(仮のやつ) index3:(仮のやつ) index4:(仮のやつ) index5:(仮のやつ) index6:(仮のやつ) index7:(仮のやつ) index8:(仮のやつ) index9:(カラム3,カラム4) ●Bテーブル PK:DETAIL_ID index1:(カラム3) index2:(質問で設定した、カラム1、カラム2) 1 PRIMARY <derived2> ALL 47688 Using temporary; Using filesort 1 PRIMARY o eq_ref PRIMARY,IDX_ORDER9 PRIMARY 4 dd.カラム3 1 Using where 2 DERIVED B ref IDX_B2 IDX_B2 10 31680 Using where しかし、上記の通り結果に変化がありませんでした。おっしゃる通りかもしれないですね。。。

icham

2015/08/29 09:43

Using temporary; Using filesortをなくさない事には早くならないので厳しいですね。解決できなくてごめんなさい。

spring

2015/09/04 08:08

いえいえ本件、おかげさまで解決出来ました。ありがとうございます!

行動規範の内容に同意します

複合インデックスを作ったことによって処理改善はされています。
aテーブルとbテーブルのカラム3にそれぞれINDEXを設定して、もう一度EXPLAINしてみてください。

投稿2015/08/26 09:25

anonymouskawa

総合スコア856

spring

2015/08/26 09:55

aテーブルに関してPKでもありますが設定しました。すみません、bのテーブルに関しては既にカラム3がインデックスに設定されていました。 a→インデックスを設定。 b→そのまま。下記は上記でEXPLAINした結果です。 1 PRIMARY <derived2> ALL 47688 Using temporary; Using filesort 1 PRIMARY o eq_ref PRIMARY,IDX_ORDER9 PRIMARY 4 dd.カラム3 1 Using where 2 DERIVED B ref IDX_B2 IDX_B2 10 31680 Using where

anonymouskawa

2015/08/26 10:02

bテーブルのPKを伺っても良いですか？

spring

2015/08/26 10:25 編集

>bテーブルのPKを伺っても良いですか？質問したsqlには載っていないカラム(仮DETAIL_ID)になります。また、確認した所Aテーブルはその他にもすでにインデックスが設定されております。 ●Aテーブル PK:カラム3 index1:(仮のやつ) index2:(仮のやつ) index3:(仮のやつ) index4:(仮のやつ) index5:(仮のやつ) index6:(仮のやつ) index7:(仮のやつ) index8:(仮のやつ) index9:(今回カラム3を設定) ●Bテーブル PK:DETAIL_ID index1:(カラム3) index2:(質問で設定した、カラム1、カラム2) 現状は上記のような感じとなっております。

spring

2015/08/26 10:26 編集

誤字修正しました。

anonymouskawa

2015/08/26 13:41

PKが何かしら絡んでいれば、パーティショニングもできるかと思ったのですがそのPKだと厳しいですね… あとは、カラム5にINDEXつけてみるとか…。それでもUsing temporary; Using filesortは取れないかもしれないです。参考 http://web.archive.org/web/20131112113740/http://www.mysqlpracticewiki.com/index.php/Extra_field

spring

2015/08/27 02:28 編集

ご回答ありがとうございます。 >PKが何かしら絡んでいれば、パーティショニングもできるかと思ったのですが >そのPKだと厳しいですね… こういう方法もあるの知りませんでした。教えて頂きありがとうございます。 >あとは、カラム5にINDEXつけてみるとか…。 >それでもUsing temporary; Using filesortは取れないかもしれないです。参考URLを頂きありがとうございます。勉強になります。下記のように設定しEXPLAINしました。結果を記載します。ほぼへんかがありませんでした。。。 ●Aテーブル PK:カラム3 index1:(仮のやつ) index2:(仮のやつ) index3:(仮のやつ) index4:(仮のやつ) index5:(仮のやつ) index6:(仮のやつ) index7:(仮のやつ) index8:(仮のやつ) index9:(カラム3,カラム4) ●Bテーブル PK:DETAIL_ID index1:(カラム3) index2:(質問で設定した、カラム1、カラム2) index3:(カラム5) 1 PRIMARY <derived2> ALL 47688 Using temporary; Using filesort 1 PRIMARY o eq_ref PRIMARY,IDX_ORDER9 PRIMARY 4 dd.カラム3 1 Using where 2 DERIVED B ref IDX_B2 IDX_B2 10 31680 Using where

行動規範の内容に同意します

ベストアンサー

何はともあれ、まずはEXPLAINをしてみてください。結果を見れば、どこが足を引っ張っているのか見えてきます。

投稿2015/08/26 07:56

maisumakun

総合スコア145184

spring

2015/08/26 08:13

ご回答いただきありがとうございます。下記のように結果が出ました。下記インデックス作業前 1 PRIMARY <derived2> ALL 47688 Using temporary; Using filesort 1 PRIMARY o eq_ref PRIMARY PRIMARY 4 dd.カラム3 1 Using where 2 DERIVED B ALL 268773 Using where 下記インデックス作業後 1 PRIMARY <derived2> ALL 47688 Using temporary; Using filesort 1 PRIMARY o eq_ref PRIMARY PRIMARY 4 dd.カラム3 1 Using where 2 DERIVED B ref IDX_B2 IDX_B2 10 31680 Using where 上記ID「2」が268773→31680になっているのは改善しているという認識であっていますでしょうか？ <derived2>の部分を解消したいですが調べていても分かりませんでした。

maisumakun

2015/08/26 08:27

JOINをかけると、いったんテンポラリテーブルを作る必要があって、このテンポラリテーブルにインデックスが効かないので、あとのGROUP BYなどは極端に遅くなってしまいます。 Aの表の中身はあとで使わないようですので、JOINは行わず、Bに対してWHERE EXISTSでAと照合させるような形として、そして（相関サブクエリが順調に進むように）適切にインデックスを振れば、GROUP BYでもBのインデックスが生きてきます。

spring

2015/08/26 09:14

ご回答ありがとうございます。すみません、訳あってこちらsqlを修正せず対応する方法はありますでしょうか？その他MySQLの設定も抜きでの作業が出来れば教えて頂けますでしょうか？

spring

2015/08/27 06:04

お忙しいところすみません、SQLに関して未熟でして、この場合質問のinner joinをEXISTS化するアドバイスを頂けますでしょうか？

spring

2015/09/04 08:11

>Aの表の中身はあとで使わないようですので、JOINは行わず、 >Bに対してWHERE EXISTSでAと照合させるような形として、そして（相関サブクエ >リが順調に進むように）適切にインデックスを振れば、GROUP BYでもBのインデック >スが生きてきます。上記の方法で解決しました。ご回答いただきありがとうございます!

行動規範の内容に同意します

あなたの回答