複数テーブルを検索するクエリの実行時間を短縮したい

Question

feedというテーブルには
site_name, site_url, feed_name, feed_url, date, count
feed_urlがプライマリーの６つのフィールドがあり、

siteというテーブルには
site_name, site_url, first_date, last_date
site_urlがプライマリーの４つのフィールドがあります。

feedテーブルのsite_urlとsiteテーブルのsite_urlには全く同じものが入っています。

この二つのテーブルを用いて、以下のような条件でレコードを取り出したいです。

・site_urlごとのfeed.countの平均値を取得
・取得した平均値をソート(DESC)
・一週間前までのfeed.dateの中から
・site_urlとsite_nameとfirst_dateとlast_dateと算出した平均値を取り出したい

以下のようなSQL文を作ってphpMyAdminから実行してみたのですが、２秒ほどかかってしまいました。より高速に取り出すことはできますでしょうか？

//0.5程度で終わる
//これプラスsiteテーブルのfirst_dateとlast_dateも取り出したい。
SELECT site_name, site_url, AVG( count ) 
FROM  `feed` 
WHERE DATE >  '2011-01-10 00:00:00'
GROUP BY site_url
ORDER BY AVG( count ) DESC 
LIMIT 0 , 30

//2秒ほどかかる
//目的のレコードが取り出せる。
//このSQLを高速化したい。
SELECT site.first_date, site.last_date, feed.site_name, feed.site_url, AVG( feed.count ) 
FROM  `feed` ,  `site` 
WHERE feed.date >  '2011-01-10 00:00:00'
AND feed.site_url = site.site_url
GROUP BY feed.site_url
ORDER BY AVG( feed.count ) DESC 
LIMIT 0 , 30

できれば１秒以内が理想なのですが、そこまで高速にすることは可能でしょうか？
もし可能な場合は、そのSQL文を教えて頂ければ幸いです。（１秒以内でなくても高速になれば嬉しいです。）

よろしくお願いします。

taka451213 · Accepted Answer

こんばんは。

まぁ、普通に書けば・・・、
select first_date, last_date, feed.site_name, feed.site_url, avg_cnt
from site
inner join 
(select site_name, site_url, avg( count ) as avg_cnt
from feed
where date > '2011-01-10 00:00:00'
group by site_url) feed
on (site.site_url = feed.site_url)
order by avg_cnt desc
limit 0, 30

こうなるわなぁ・・・。
SQLのチューニングもそうですが、件数やカーディナリティによって、適切にINDEXを付けると効果的です。
まずは実行計画などを見て、どこにコストがかかっているかを見てください。
ちなみにSQLの最初に、EXPLAIN SELECT・・・で書き始めると見られます・・・。

taka451213 · Answer

こんにちは。

既に＃２、＃３さんが回答を出しておられますが・・・、
実行計画を見ると、
１．feedテーブルの検索がINDEXなし（ALL）、487226件
２．siteテーブルの検索がPRIMARY、1件
３．結合後にUsing filesort、257件
となっております。

２は問題なし。
３は致し方なし（ORDER BYに含まれるカラムにINDEXを付けられればいいのですが、この場合はAVGの計算結果のため、無理という事。件数的にも少ないので、許容範囲）・・・。
１は問題あり。
です。

実行計画の結果は、このように見る事ができます。

nora1962 · Answer

An.2です。
そうです。
create index idx_feed on feed ( `date`, `site_url`);
ですね。

nora1962 · Answer

出されている情報だけでは実行時間を短縮できるかどうかは分かりません。
まず、ＥＸＰＬＡＩＮで現在の実行計画を取得して見てください。

後、これは推測になりますが`feed` に（`DATE`,`site_url`）で索引を作成し、
SELECT site.first_date, site.last_date, F.site_name, F.site_url, F.AVG_COUNT
FROM
( SELECT feed.site_name, feed.site_url, AVG( feed.count ) AVG_COUNT
FROM `feed`
WHERE feed.date > '2011-01-10 00:00:00'
GROUP BY feed.site_url ) F INNER JOIN `site` ON F.site_url = site.site_url
ORDER BY F.AVG_COUNT DESC
LIMIT 0 , 30
とした方がもしかすると実行時間は少なくてすむかもしれません。
（あまり根拠ありませんが）

複数テーブルを検索するクエリの実行時間を短縮したい

こんばんは。

こんにちは。

An.2です。

出されている情報だけでは実行時間を短縮できるかどうかは分かりません。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング