時間のかかるqueryの対処法

質問

スロークエリログで、特定のクエリが遅いことが分かりました。

テーブル構成は以下のような感じです。

テーブル：shoptable
データ数：200件（店舗数）ぐらい

id　category shopname　　 area
1　　　2　　　佐藤商店　　東京都
2　　　1　　　田中衣服　　北海道
3　　　5　　　柴田ホーム　名古屋

テーブル：goodstable
データ数：25万件（商品数）ぐらい

id　shopid　goods　　　　date
1　　2　　　帽子　　　　2012-06-01 03:06:56
2　　2　　　ジャケット　2012-06-01 03:10:12
3　　1　　　ライター　　2012-06-01 03:15:22
4　　3　　　くぎ　　　　2012-06-01 03:55:07

このような状況で、categoryが2のお店の商品を日付順に100コ目以降から10コだけ表示したく、こんなqueryを試したのですが、

select * from goodstable as g left join shoptable as s on g.shopid = s.id where category='2' order by g.date desc LIMIT 100,10;

表示されるまでに時間がかかってしまいます。

expainで見たところrowsで25万件すべてをチェックしているようなので、それが原因かなと思っているのですが、どのようなqueryにしたらもっと早く結果を得れることができるでしょうか。

回答No.3

Siegrune · ベストアンサー

ANO.2です。

＞ただ、店舗や商品の数が今後も増えていくのですが、その都度、インデックスを張りなおさないといけないのでしょうか。

インデックスは、店舗や商品を増やすたびに更新されます。

要は、インデックスを作ると、テーブルに対する追加・更新・削除のときに、
インデックスも更新されるので、テーブルに対する追加・更新・削除が遅くなる。
（但し、今回みたいに１種類や２種類増やしたところで、人間の目では遅くなったことはわからないでしょう。）
そのかわりに検索処理のときにインデックスを使って検索するので非常に早くなる。
ということです。

スロークエリログの件は・・・私はちょっとわからないです。
（スロークエリログを使ったことがないので。）
他の方が回答してくれるかも。。。

お礼

インデックスの件、ありがとうございました。
とても分かりやすい説明でありがたかったです。

回答No.2

Siegrune

create index ix1_shoptable on shoptable(category,id)
と
create index ix1_goodstable on goodstable(shopid,id)

を作るだけでずいぶん早くなるような気がします。

お礼

たびたび申し訳ありません。

同じクエリにも関わらず、explainで見たときのrowsとスロークエリログに出力されたrows_examinedの値が全然異なっていました。

explainでみると、keyには先程作成したインデックスがちゃんとあり、rowsは2000ぐらいの値でした。

しかしスロークエリログだと
# Query_time: 8 Lock_time: 0 Rows_sent: 10 Rows_examined: 327174
このように出力されていました。

この違いは何が原因なのでしょうか。

補足

すごく早くなりました！
ありがとうございます。

ただ、店舗や商品の数が今後も増えていくのですが、その都度、インデックスを張りなおさないといけないのでしょうか。

それとも自動でインデックスは増えていくでしょうか。

回答No.1

yambejp

explainするときにselect * from・・・をすると
全てのカラムがインデックスされていない限りインデックが有効には
ならないでしょう
抽出したいカラムを列記し、適切なインデックスをはってください

もしかしたら
select * from goodstable as g inner join shoptable as s on g.shopid = s.id and category='2' order by g.date desc LIMIT 100,10;
の方が若干効率的かもしれません

補足

ありがとうございます。

教えて頂いたクエリーで試したところ、おっしゃるとおり若干早かったです。
2.22secが1.99secになりました。
できれば1sec以下にしたいのですが、難しそうですね。

抽出したいカラムはshopname,area,goods,dateになります。

インデックスは具体的にどのようにはったらいいのでしょうか。

explainでみたときExtraにusing filesortがあったのですが、これがあるとorder byにインデックスを使用できないと書いてありました。

create index shopname on shoptable(shopname);
create index area on shoptable(area);
create index goods on goodstable(goods);
create index date on goodstable(date);

こんな感じでいいのでしょうか？