クエリが数秒かかる

質問

こんにちは、データベース初心者です。
――――
id | tag
――――
1 | 1002
1 | 1004
1 | 4005
2 | 3001

というような、IDに複数の検索用タグが付いているテーブルに対して、

select TB2.id
from tb as TB1
inner join tb as TB2 on TB2.id=TB1.id
where TB1.tag=1002 and TB2.tag=1004
limit X,Y;

というような、任意の数（例は2個）の指定したtagを全て含むIDを検索するクエリがあります。
（指定が3個の場合は単純にinner join を増やしています）

で、指定のtagが1つの場合は一瞬で返されるのですが、
複数指定の場合に何秒もかかるときがありまして、
一瞬で返させるには一体どこを直せばよいのかがわからないのです。
クエリを変えるのか、テーブル構成を変えるのか、マシンを変えなきゃ無理なのか、
こういった場合のセオリーなど、ご指導願いたいのです。

２つ目の質問として、
order by が無い場合というのは、データに大きな変更が無い期間中は、
同じクエリであれば同じ順序で返ってくると考えて良いのでしょうか？
というのも、上記クエリにorder byを付けたかったのですが、
ソート項目が多数あり断念しまして、ただページング機能は付けたく思いまして。

環境は、ローカルで、winXP、MySQL 5.1、PHP経由で、ブラウザから操作・表示しています。
id数が約1000万、tagは500種ほどあり、1idに対して1～30個ほどで平均8個くらい付いており、
テーブルのレコード数は約8000万となっています。

素人質問で恐縮ですがよろしくお願いします。

回答No.3

yambejp

ごめんなさい元のSQLをコピっちゃいました
こちらで

select id
from tb
where tag IN(1002,1004)
group by id
having count(distinct tag)=2

お礼

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

試しましたところ、最初は強制終了するほど遅かったのですが、
仰るとおり適切なインデックスを使ってくれなかったようで、
use index で(tag,id)の複合?インデックスを指定しましたところ、
4秒くらいで返すようになりました。

で、何度か試しているうちにin句に同じtag、同じ順序で指定したにも関わらず、
なぜか短縮され、2秒くらいで返すようになりました。
試し方が悪かったのかも知れませんが、その後は一応安定しており、
また、教えて頂いたSQLの方が組みやすく、非常に勉強になりました。

冒頭で「とりあえず」と仰っているので、第二第三の策が気になりますが、
この度は誠にありがとうございました。

回答No.2

yambejp

とりあえず

select TB2.id
from tb as TB1
inner join tb as TB2 on TB2.id=TB1.id
where TB1.tag=1002 and TB2.tag=1004

ただし、idとtagをつかったindexが設定されていないとスピードは
保障されません

回答No.1

kosukejlampnet

１）EXPLAINを使って分析し、インデックスを適切に設定する
２）ORDERをつけない場合常に同じ順番で取得する保証は一切ありません。

お礼

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

１）btreeというタイプのインデックスを両カラムに指定しており、
explainではusing indexと出ています。
他にはusing temporaryとusing whereというのも出ているのですが、
正直explainを見て、よしじゃぁこうした方がいいなというのが思い付きません。
勉強不足なのは承知していますが、何かアドバイスありましたら宜しくお願いします。

２）保証がない以上ページング目的で同順を期待するのはやめた方が良さそうですね…。

貴重なご意見ありがとうございました。