複数テーブルを検索するクエリの実行時間を短縮したい
- feedテーブルとsiteテーブルを結合したクエリの実行時間を短縮する方法について教えてください。
- 現在のクエリでは2秒ほどかかってしまうため、1秒以内での高速化を目指しています。
- 目的はfeedテーブルのsite_urlごとのcountの平均をソートし、一週間前までのデータを取り出すことです。
- ベストアンサー
複数テーブルを検索するクエリの実行時間を短縮したい
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文を教えて頂ければ幸いです。（１秒以内でなくても高速になれば嬉しいです。） よろしくお願いします。
- jimascript
- お礼率20% (10/48)
- MySQL
- 回答数4
- ありがとう数4
- みんなの回答 （4）
- 専門家の回答
質問者が選んだベストアンサー
こんばんは。 まぁ、普通に書けば・・・、 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・・・で書き始めると見られます・・・。
その他の回答 (3)
- taka451213
- ベストアンサー率47% (436/922)
こんにちは。 既に＃２、＃３さんが回答を出しておられますが・・・、 実行計画を見ると、 １．feedテーブルの検索がINDEXなし（ALL）、487226件 ２．siteテーブルの検索がPRIMARY、1件 ３．結合後にUsing filesort、257件 となっております。 ２は問題なし。 ３は致し方なし（ORDER BYに含まれるカラムにINDEXを付けられればいいのですが、この場合はAVGの計算結果のため、無理という事。件数的にも少ないので、許容範囲）・・・。 １は問題あり。 です。 実行計画の結果は、このように見る事ができます。
お礼
２度も回答ありがとうございました。 INDEXとか正直全然わかってなかったので、これを期に基本的な部分を勉強していきたいと思いました。 本当にどうもありがとうございます。 ベストアンサーはかなり迷いましたが、回答内容で比べることがちょっと難しかったので、時間的に先に回答をくれたtaka451213さんを選ばせてもらいました。
- nora1962
- ベストアンサー率60% (431/717)
An.2です。 そうです。 create index idx_feed on feed ( `date`, `site_url`); ですね。
お礼
なんと0.1で終わるようになりました…！どうもありがとうございます。これからは自力で解決できるようSQLの勉強もがんばります。本当にありがとうございました。
- nora1962
- ベストアンサー率60% (431/717)
出されている情報だけでは実行時間を短縮できるかどうかは分かりません。 まず、ＥＸＰＬＡＩＮで現在の実行計画を取得して見てください。 後、これは推測になりますが`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 とした方がもしかすると実行時間は少なくてすむかもしれません。 （あまり根拠ありませんが）
お礼
お二人とも凄いですね…どちらのSQL文も0.5秒くらいでおわりました。ありがとうございます。 `DATE`,`site_url`で索引というのは単純にインデックスをつけるだけでよいのでしょうか？
関連するQ&A
- Acess2003で複雑なSQL?
Access2003で以下のSQLを実行させることはできるのでしょうか？ 直接SQLで実行する方法と、Access独特の両方で実行する方法が知りたいです。 select name, count(*) from テーブル group by name order by 2 desc;
- 締切済み
- オフィス系ソフト
- 複数の項目をキーとする検索について
Oracle9i Enterprise Edition R2の環境下で 以下のようなトランザクション、マスタテーブルより １SQLにて[Output]の内容を抽出したいと思っております。 ****************************************** [A]テーブル・・・トランザクション(20万件) ID first_name last_name code1 code2 rank ****************************************** [B]テーブル・・・マスタ(10万件) class_name code1 code2 B.code1+B.code2でユニーク ****************************************** [Output]・・・抽出結果 A.ID A.first_name A.last_name B.class_name A.rank 結合のキーはA.code1+A.code2=B.code1+B.code2 [Output]には[A]を全件を出力したい ****************************************** [補足] A.code1(ex.12345)・・・NULL有 A.code2(ex.001)・・・NULL有 B.code1(ex.12345)・・・NULL、ダブリ無 B.code2(ex.001)・・・NULL、ダブリ無 以下、質問です。 A.ID A.first_name A.last_name B.class_name A.rank A.code1||A.code2 = B.code1||B.code2(+) このようなＳＱＬは発行できないことは確認済みなのですが、 これ以外で[Output]のような抽出結果をSQLにて抽出するには どのようにするのが一番効率的でしょうか？ できましたら、INDEXの張り方等も含め、SQL作成のヒントを いただけたら幸いです。
- ベストアンサー
- Oracle
- Mysqlで複数テーブルの参照
Mysqlで複数テーブルの参照 お世話になります。Mysqlについて質問させてください。 現在、それぞれnameとdateとflagの３フィールドをもつテーブルが、２つあります。 もともと別の用途で準備したものなのですが、 ２つのテーブルのflagを参照して、1のものだけdate順に並べることが出来るでしょうか？ ■テーブル１ ----------------------------- name ｜ date ｜flag｜ ----------------------------- 田中 ｜ 2010-06-01 ｜ 1 ----------------------------- 山田 ｜ 2010-09-01 ｜ 1 ----------------------------- 田辺 ｜ 2010-10-01 ｜ 2 ----------------------------- 田所 ｜ 2010-11-01 ｜ 2 ----------------------------- ■テーブル２ ----------------------------- name ｜ date ｜flag｜ ----------------------------- 加藤 ｜ 2010-10-01 ｜ 1 ----------------------------- 佐藤 ｜ 2010-07-01 ｜ 1 ----------------------------- 織田 ｜ 2010-12-01 ｜ 2 ----------------------------- 斎藤 ｜ 2010-01-01 ｜ 2 ----------------------------- ■求める結果 ----------------------------- name ｜ date ｜flag｜ ----------------------------- 田中 ｜ 2010-06-01 ｜ 1 ----------------------------- 佐藤 ｜ 2010-07-01 ｜ 1 ----------------------------- 山田 ｜ 2010-09-01 ｜ 1 ----------------------------- 加藤 ｜ 2010-10-01 ｜ 1 ----------------------------- 具体的なSQL文でなくとも構いません。参考になるような情報、サイトをご存じでしたら、教えてください。 よろしくお願いします。
- ベストアンサー
- MySQL
- 複数のテーブルに跨る集計その２
こんにちは。お世話になっております。 http://oshiete1.goo.ne.jp/qa2684315.html 昨日、上記ページより投函させていただきましたが、その時は解決できたものの、その後の新たなソースの追加で上記ページにある、検索結果表示にある、各々の「件数表示」が出来なくなってしまいました。 当初は単に追加したソースが邪魔してる？などという思いから、色々と設定を変えているものの上手く行かず、現在なんとか件数表示を表示する事は出来たものの、繰返処理（while）の中に新たに、 $id = $row['id']; $sql = "select id FROM data WHERE id = '$id'"; $result = mysql_query($sql); $rows = mysql_num_rows($result); echo $rows; なんて、入れることで対処出来ましたが、この対処法に自信がなく、改めて諸先輩方々にヒントだけでもご指導いただければと投函させて頂きました。 お忙しい中恐縮ですが宜しくお願い致します。 ※通常の？検索ソースは上記ページでご教授いただいた select M.id, M.name, count(M.name) as count from data D inner join member M on M.id = D.id group by M.name, M.id order by count desc を参考にさせていただいております。
- ベストアンサー
- MySQL
- MｙSQLでの複数テーブル（４つ）への検索について
はじめまして、こんばんは。 現在、ECサイトを構築中なのですが、MySQLにPHPから検索をかけたいのですが、どうにも詰まってしまったので、投稿させて頂きました。 PHPバージョン：PHP 5.2.6 DBバージョン：MySQL 4.1.22 機能としては、購入履歴から、購入商品のランキングを作りたいと思っております。 ランキング自体の表示はOKなのですが、そこに商品のカテゴリーを表示させたいのです。 関係するテーブルは以下の通りです。 【order】購入履歴テーブル product_id：商品ID 【products】商品テーブル product_id：商品ID name：商品名 image：商品画像 【p_category】商品カテゴリーテーブル product_id：商品ID category_id：カテゴリーID 【category】カテゴリーテーブル category_id：カテゴリーID category_name：カテゴリー名 parent_category_id：親カテゴリーID 【表示させたい項目】 product_id：商品ID name：商品名 image：商品画像 category_id：カテゴリーID category_name：カテゴリー名 parent_category_id：親カテゴリーID 現在のソースは以下の通りです。 --------------------------------------- SELECT count(o.product_id) as rank, name, image, p.product_id FROM order as o, products as p WHERE o.product_id=p.product_id GROUP BY name, image, p.product_id ORDER BY rank desc limit 5 --------------------------------------- 上記にプラスしたいのは、 p.product_idと【p_category】のproduct_idが一致したcategory_idを取得して、【p_category】のcategory_idと【category】category_idが一致したcategory_name、parent_category_idも取得したいです。 分かりにくいうえに、長文になってしまいましたが、ご指導・ご鞭撻のほど宜しくお願い申し上げます。
- ベストアンサー
- MySQL
- mySQL 複数テーブルから検索したい
はじめまして、よろしくお願いします。 職場環境がかわり、はじめてmySQLを使用しています。 ３つのテーブルから情報を検索したいのです。 [テーブル名: cast] name // PRIMARY KEY age (例 なまえ１, 22 なまえ２, 28 なまえ３, 25 [テーブル名: photo] name order // 写真表示順 (INT) url // 写真格納先URL (例 なまえ１, 0, http://なまえ１A.jpg なまえ１, 1, http://なまえ１B.jpg なまえ１, 2, http://なまえ１C.jpg なまえ３, 0, http://なまえ３A.jpg [テーブル名: schedule] name workday // 出勤日 (DATE) starttime // 出勤時間（TIME) finishtime // 退勤時間(TIME) (例 なまえ３, 2013-02-04, 9:00, 17:00 なまえ２, 2013-02-05, 10:00, 18:00 なまえ２, 2013-02-06, 10:00, 18:00 なまえ２, 2013-02-07, 10:00, 18:00 キャスト情報(cast)とそのキャストの写真格納先URL(photo)とキャストの出勤時間(schedule)のテーブルです。 ※キャストは何枚でも写真を登録できます。登録していない場合もあります。 キャストのリストを表示したいです。 表示したい項目は cast.name, cast.age, photo.url, schedule.starttime, schedule.finishtime になります。 期待する結果は（２月５日の場合) なまえ１, 22, なまえ１A.jpg, NULL, NULL なまえ２, 28, NULL, 10:00, 18:00 なまえ３, 25, なまえ３A.jpg, NULL, NULL すべてのキャストを表示します。 photo.urlはphoto.order=0のもの（photo.order=0の写真をサムネイル表示に使っています) ２月５日に出勤情報があれば表示 いろいろ考えたのですが、どうしても photo にデータがない（"なまえ２"）のところでつまづいてしまって、どうにも。。。 とりあえずPHPでそれぞれのテーブルの情報を取得して 自分で UNION、 USING('name') みたいな処理してますが、ソースがとっても恰好悪いです。 ズバッとしびれるSQL文をどうかご教示していただきたく、投稿しました。 よろしくお願いいたします＞＜
- ベストアンサー
- MySQL
- 複数テーブルの削除
お世話になります。 うまい資料が見つからなかったので質問させてください。 例えば、下記のようなテーブルが3つあったとします。 <table1> id name date <table2> id tel <table3> id etc name,date,tel,etcは同タイミングで一括に登録され、3テーブルともに共通のidで保存されます。 この時、table1のdate(日付が入ります)が30日以上経過しているデータについて、いずれのテーブルからも削除したいと思っています。 こんな場合、どんなSQL文を書けば良いのでしょうか？ お分かりになる方、何卒よろしくお願いしますm(_ _)m
- ベストアンサー
- MySQL
- SQLの書き方
select A.NO, A.NAME, count(B.TEN) FROM T1 A, T2 B GROUP BY A.NO ORDER BY 3 DESC 結果として以下のようになってほしいのですが、どのようにSQLを書けばよいのでしょうか？ 結果 NO | NAME | TEN ---+------+----- 3 + 上村 + 40 1 + 吉田 + 11 2 + 浅野 + 6 T1テーブル NO | NAME ---+-------- 1 + 吉田 2 + 浅野 3 + 上村 T2テーブル NO | TEN ---+-------- 1 + 10 2 + 5 3 + 0 1 + 1 2 + 1 3 + 40
- ベストアンサー
- Oracle
- 特定のデータの前後を取得したい
以下のようなテーブルがあり、 SELECT id , regist_date FROM table_name ORDER BY regist_date DESC; ↑このSQLで並べると↓以下になるとします。 id(int型) regist_date(datetime型) 12 2017-03-30 08:05:03 95 2017-03-29 19:05:03 72 2017-03-28 12:05:03 15 2017-03-28 12:05:03 62 2017-03-27 15:05:03 94 2017-03-26 12:05:03 やりたい事はidが72というのが分かっており、 そのデータと前後のデータを取得したいです。 ※日付の部分が完全に重複するデータが存在する場合もあります。 ※idは重複しません。 ↓このデータがとりたいです。 95 2017-03-29 19:05:03 72 2017-03-28 12:05:03 15 2017-03-28 12:05:03 SELECT * FROM table_name WHERE id = 72 ORDER BY regist_date DESC; ここから先が分からなくなってしまいどなたかわかる方いらっしゃいますか？
- ベストアンサー
- MySQL
- 複数テーブルからの抽出で時間にムラがでます
はじめまして。すみませんが教えてください。 １秒に１レコードで１日(60秒×60分×２４時間)１テーブル(86400レコード)を作成しています。 １０日で１０テーブル作成します。 複数のテーブルから開始日時から終了日時までの範囲内で、かつ、ID1フィールドに１がセットされているデータを抽出するＳＱＬを記述しておりますが、処理時間にむらがあります。 ＳＱＬの記述に問題があるのでしょうかお教えください。 ACCESS2000で.NETで開発しております。 sDTフィールドは主キーでDateTime型にしています。 SELECT T_1.sDT, T_1.DT1, T_1.DT2 FORM T_1 WHERE T_1.sDT BETWEEN #2010/02/16 12:00:00# AND #2010/02/10 20:00:00# AND T_1.ID1=1 UNION ALL SELECT T_2.sDT, T_2.DT1, T_2.DT2 FORM T_2 WHERE T_2.sDT BETWEEN #2010/02/16 12:00:00# AND #2010/02/10 20:00:00# AND T_2.ID1=1 UNION ALL SELECT T_3.sDT, T_3.DT1, T_3.DT2 FORM T_3 WHERE T_3.sDT BETWEEN #2010/02/16 12:00:00# AND #2010/02/10 20:00:00# AND T_3.ID1=1 ORDER BY T_1.sDT DESC; ExecuteReader実行時の処理時間を計測すると だいたい800msecなのですが、数回に一回25000msec掛かったりします。 すみませんがよろしくお願いします。
- 締切済み
- その他(データベース)
お礼
凄いですね…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=""><derived2></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=""> </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件のレコードがあります。