【MySQL 4.1.xでサブクエリが遅い】


[table01]
table01_id│field
────┼───
1   │テキスト
2   │テキスト

[table02]
table02_id│table01_id│field_id
────┼────┼──
1    │1   │1
2    │1   │2
3    │2   │2
4    │1   │4
5    │2   │3
6    │1   │5


この様な感じのテーブルで、table01とtable02は、table01_idでCASCADEしています。
(本当は、もっとイッパイCASCADEしてて、複雑ですが、とりあえず)

SELECT `table01`.`table01_id`
FROM table01
WHERE `table01`.`field` <> ''
IN (
SELECT `table02`.`table01_id`
FROM table02
WHERE `table02`.`field_id` = '1'
OR `table02`.`field_id` = '4'
OR `table02`.`field_id` = '5'
GROUP BY `table02`.`table01_id`
HAVING COUNT( `table02`.`table01_id` ) =3
)
GROUP BY `table01`.`table01_id`;

というSQLを実行したいのですが、サブクエリを使っているせいか、
異様に遅いのです。

JOINでなんとか、これを実現できませんか?

-----

↓これは違いますもんね。。。

SELECT `table01`.`table01_id`
FROM table01
LEFT JOIN `table02` ON `table01`.`table01_id` = `table02`.`table01_id`
WHERE `table01`.`field` <> ''
AND (
`table02`.`field_id` = '1'
AND `table02`.`field_id` = '4'
AND `table02`.`field_id` = '5'
)
GROUP BY `table01`.`table01_id`;

回答の条件
  • 1人2回まで
  • 登録:2009/07/28 14:55:29
  • 終了:2009/07/29 11:17:56

ベストアンサー

id:HALSPECIAL No.2

HALSPECIAL回答回数407ベストアンサー獲得回数862009/07/28 18:03:45

ポイント60pt

未確認ですが、こちらでどうでしょうか


SELECT TBL1.`table01_id`
FROM table01 AS TBL1
INNER JOIN (
    SELECT `table01_id`
    FROM table02
    WHERE `field_id` IN ('1','4','5')
    GROUP BY `table01_id`
    HAVING COUNT( `table01_id` ) =3
) AS TBL2
ON TBL1.`table01_id` = TBL2.`table01_id`

id:keijiro

おお!

JOIN版ですね。

試してみます。

 

ありがとうございます。

-----


かなり速度が上がりました。

30秒→0.8秒

ありがとうございました。

2009/07/29 11:14:01

その他の回答(1件)

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012009/07/28 17:41:22

ポイント20pt

もっと複雑ということなので具体的には、EXPLAIN の結果を見てということになりますが、

とりあえず、インデックスを利用できるようであれば、それなりに高速になりますので、

例えば table02 の table01_id にインデックスを付けてあるかなどを確認してみてください。

(JOINに変更してもインデックスが適正でないと、あまり高速化は見込めません)


IN ではなく EXISTS のほうが高速になる場合もありますので検討にいれてみてください。

EXISTS(
SELECT *
FROM `table02` T2
WHERE T2.`table01_id` = `table01`.`table01_id`
AND T2.`field_id` IN('1', '4', '5')
GROUP BY T2.`table01_id`
HAVING COUNT(*) =3
)

※テーブル名はエイリアス(別名)をつけるほうが見やすくなりますし、

 メンテナンスも楽になりますのでお勧めします。

id:keijiro

ありがとうございます。

 

> インデックス

これは、CASCADEしてるので、自動的に付いているような気もしますが、明示的にもつけてみました。

でも、やっぱり、変化はありませんでした。

 

> EXISTS

おお!これは、試してみます!

 

-----

 

少し速度が上がりました。

2009/07/29 11:13:33
id:HALSPECIAL No.2

HALSPECIAL回答回数407ベストアンサー獲得回数862009/07/28 18:03:45ここでベストアンサー

ポイント60pt

未確認ですが、こちらでどうでしょうか


SELECT TBL1.`table01_id`
FROM table01 AS TBL1
INNER JOIN (
    SELECT `table01_id`
    FROM table02
    WHERE `field_id` IN ('1','4','5')
    GROUP BY `table01_id`
    HAVING COUNT( `table01_id` ) =3
) AS TBL2
ON TBL1.`table01_id` = TBL2.`table01_id`

id:keijiro

おお!

JOIN版ですね。

試してみます。

 

ありがとうございます。

-----


かなり速度が上がりました。

30秒→0.8秒

ありがとうございました。

2009/07/29 11:14:01
  • id:b-wind
    1点確認。
    `table02`.`field_id には INDEX はついているか?

    あとこの手のサブクエリは MySQL は苦手なので JOIN を上手く活用した方が早そう。
    最新版なら色々最適化が入っているようだが。
  • id:kn1967
    >`table02`.`field_id には INDEX はついているか?

    サンプルではfield_idの取る値は1から5までの5種類。
    現実のほうでも同じように数種類程度であるとするならば、
    インデックスの利用で発生するオーバーヘッドと相殺されそうな気がして、
    書かなかったんだけど、どうだろう?


    >自動的に付いているような

    インデックスは勝手に付く物ではありませんし、インデックスをつければ絶対に良いのかと言えば、
    インデックスを使わない形で実行プランが計画されてしまう可能性もあるので万全とは言えない。
    そこで回答の冒頭でも書いたのですがEXPLAINで実際にどのような実行プランが立てられたのかを、
    出力させて、検討を重ねる必要性があったりする訳です。

    EXPLAINを実施せずとも、すべての結合条件が明らかになっていれば、
    ある程度は経験則でSQLを組めたりもしますが、一部抜き出しなので、
    とりあえず使いやすいて、ある程度速度も期待できるEXISTSという提案をしました。
    最初にある程度展開作業を要するJOINよりも適宜処理が走って順次応答が返ってくるほうが、
    WEB系では都合良いだろうという考えからなのですが
    データ量自体がそれほど多くないのであればJOINのほうが早いかもしれませんね。
    適宜試しながら作業進めてみてください。
  • id:keijiro
    コメントを見逃しており、閉じてしまいました m(_ _)m
     
    Re: b-wind さま
     
    >`table02`.`field_id には INDEX はついているか?
    ついています。
     
    >あとこの手のサブクエリは MySQL は苦手なので JOIN を上手く活用した方が早そう。
    ありがとうございます。
    JOINの方が早くなりました。
     
     
    Re:kn1967 さま

    >インデックスは勝手に付く物ではありません
    そうなのですね。
    お恥ずかしい事ながら、CASCADEを作るときは、MySQL Administratorでやった方が楽なので、それで作っていたのですが、
    CASCADE時にForeign Keysをつけるので、それが自動的にINDEXになるのかと勘違いしておりました。
     
     
    >適宜試しながら作業進めてみてください。
    ありがとうございます。
    EXPLAINの結果を見ながら類似のプロジェクトで試してみましたが、
    (詳細はコピペしたいのですが、案件なので、別文字に変えないとまずそうなのですが、
     複雑すぎてちょいと断念しました。)
    場合によっては、EXISTSの方が早いこともありました。
     
    ありがとうございました。
  • id:kn1967
    http://dev.mysql.com/doc/refman/5.1/ja/connector-vstudio-using-tables.html

    MySQL Administrator は使ったこと無いので、判りません。
    もしかしたら自動的にインデックス作成してくれているかもしれません。

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

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

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

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