こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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


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

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




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



//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



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

よろしくお願いします。

投稿日時 - 2011-01-17 14:35:34

QNo.6454320

すぐに回答ほしいです

質問者が選んだベストアンサー

こんばんは。

まぁ、普通に書けば・・・、
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・・・で書き始めると見られます・・・。

投稿日時 - 2011-01-17 20:04:36

お礼

凄いですね…0.5秒くらいで取り出せました!ありがとうございます。
質問の内容的にはこれで解決→終りなのですが、もう少しだけ質問させてください。

EXPLAINをつけて実行すると、下記のようなテーブルが表示されたのですが、googleさんに聞いてみてもどこがどうなって、どう直せばいいのかさっぱりでした…。


※プレーンテキストだと整形がうまくいきそうにないのでHTMLで失礼します。
<html>
<head>
<style>
td,th{padding:0 8px;border:1px solid #000;}
</style>
</head>
<body>
<table>
<tr><th>id</th><th>select_type</th><th>table</th><th>type</th><th>possible_keys</th><th>key</th><th>key_len</th><th>ref</th><th>rows</th><th>Extra</th></tr>
<tr>
<td align="right" class=" nowrap">1</td>
<td class="">PRIMARY</td>
<td class="">&lt;derived2&gt;</td>
<td class="">ALL</td>
<td class=""><i>NULL</i></td>
<td class=""><i>NULL</i></td>
<td class=""><i>NULL</i></td>
<td class=""><i>NULL</i></td>
<td align="right" class=" nowrap">257</td>
<td class="">Using filesort</td>
</tr>
<tr>
<td align="right" class=" nowrap">1</td>
<td class="">PRIMARY</td>
<td class="">site</td>
<td class="">eq_ref</td>
<td class="">PRIMARY</td>
<td class="">PRIMARY</td>
<td class="">767</td>
<td class="">feed.site_url</td>
<td align="right" class=" nowrap">1</td>
<td class="">&nbsp;</td>
</tr>
<tr>
<td align="right" class=" nowrap">2</td>
<td class="">DERIVED</td>
<td class="">feed</td>
<td class="">ALL</td>
<td class=""><i>NULL</i></td>
<td class=""><i>NULL</i></td>
<td class=""><i>NULL</i></td>
<td class=""><i>NULL</i></td>
<td align="right" class=" nowrap">487226</td>
<td class="">Using where; Using temporary; Using filesort</td>
</tr>
</table>
</body>
</html>


さらに高速にすることが可能ということでしょうか?

feedテーブルは487226件のレコードがあり、
siteテーブルは287件のレコードがあります。

投稿日時 - 2011-01-18 00:49:35

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

こんにちは。

既に#2、#3さんが回答を出しておられますが・・・、
実行計画を見ると、
1.feedテーブルの検索がINDEXなし(ALL)、487226件
2.siteテーブルの検索がPRIMARY、1件
3.結合後にUsing filesort、257件
となっております。

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

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

投稿日時 - 2011-01-18 11:22:53

お礼

2度も回答ありがとうございました。
INDEXとか正直全然わかってなかったので、これを期に基本的な部分を勉強していきたいと思いました。
本当にどうもありがとうございます。

ベストアンサーはかなり迷いましたが、回答内容で比べることがちょっと難しかったので、時間的に先に回答をくれたtaka451213さんを選ばせてもらいました。

投稿日時 - 2011-01-19 15:20:41

ANo.3

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

投稿日時 - 2011-01-18 01:26:13

お礼

なんと0.1で終わるようになりました…!どうもありがとうございます。これからは自力で解決できるようSQLの勉強もがんばります。本当にありがとうございました。

投稿日時 - 2011-01-19 15:16:41

ANo.2

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

後、これは推測になりますが`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
とした方がもしかすると実行時間は少なくてすむかもしれません。
(あまり根拠ありませんが)

投稿日時 - 2011-01-17 21:37:45

お礼

お二人とも凄いですね…どちらのSQL文も0.5秒くらいでおわりました。ありがとうございます。

`DATE`,`site_url`で索引というのは単純にインデックスをつけるだけでよいのでしょうか?

投稿日時 - 2011-01-18 01:03:52

あなたにオススメの質問