created_atやmodified_atにインデックスを貼る意味

Question

###DBのインデックスの貼り方への疑問
お世話になっております。
多対多のcategory_itemsという中間テーブルに置いて

id	item_id	category_id	modified_at	created_at
int	int	int	timestamp	timstamp

(category_id,modified_at)のキーペアにインデックスが貼ってあったのですが、
これってパフォーマンス上何か有利になりますか？

意図的には

select items.name,items.price from category_items 
left join items on items.id = category_items.item_id
where category_items.item_id = $category_items.item_id
order by modified_at asc limit $number

といった感じの処理を早くしたいのかなと思ったのですが、インデックスを貼るとorder byの動作に影響を与えるかどうか気になったので、
教えていただければと思います。

Answer 1

(category_id,modified_at)のキーペアにインデックスが貼ってあったのですが、
これってパフォーマンス上何か有利になりますか？

たぶん下記の３行に効果での(category_id,modified_at)のキーペアだと思われますが
on区とwhere区ではitem_idですので上記インデックスは使われません
order byでは結果に対する並び替えですので、インデックスは使われないのではないでしょうか。(抽出時に有効です）

left join items on items.id = category_items.item_id
where category_items.item_id = $category_items.item_id
order by modified_at asc limit $number

Comments

[octcat]

ありがとうございます。
キーペアでのクエリを想定していたけど結局使われていなかったか削除されたと推測します。
該当のインデックスは外そうと思います。

Answer 2

explainで実行計画を取ると分かるかと。

SQLから意図が読み取りにくので、想定になるのですが、
category_idとmodified_atの複合インデックスということなので、
対象のSQLでは効果は得られません。
オプティマイザがいい感じに解釈してくれれば有効かもしれませんが、
order byをcategory_id,modified_atにしたほうが確実です。

中間テーブルということなので、数千件しかないことはないと思われるのですが、
limitをかけない時の件数が3～4千件の場合もインデックスが利用されないので、
パフォーマンスに影響はありません。

余計な詮索ですいませんが、
このSQLですが中間テーブルから全件持ってきて、
最近更新された数件を取得するような処理で間違いないでしょうか。

Comments

[octcat]

ありがとうございます。
僕も意味ないのでは、って思っていて

>オプティマイザが解釈してくれれば

この点でインデックスを貼った方はそういうことを考えていたのかなと推測していますが多分そういう影響もないですよね。

>余計な詮索ですいませんが、
このSQLですが中間テーブルから全件持ってきて、
最近更新された数件を取得するような処理で間違いないでしょうか。

あーすいません。その通りです。クエリを書き間違えていますね。修正します。

[szk.]

order byをcategory_id,modified_atにすると、並び順変わっちゃいますね、すいません。

インデックスを貼った人とSQLを書いた人が別なんじゃないでしょうか。
もしくは別のSQL用のインデックスだという可能性もあるかと思います。
記載のSQLとインデックスは有効に働きません。

[octcat]

承知いたしました。ありがとうございます。
思った通りで安心しました。