遅いクエリーを改善するため他の書き方を教えて欲しい

質問

mysqlで特定のクエリーが遅いのですが、どのようにしたら改善されるでしょうか。

問題のクエリは下記になります。

mysql_query("select i.id AS id,i.name AS iname,i.price AS iprice,v.id AS vid,v.vote AS vvote,SUM(IF(v.vote='10',1,0)) AS totalvote from item AS i LEFT JOIN vote AS v ON i.id=v.id GROUP BY i.id order by totalvote desc;",$conn);

「itemテーブルのid」と「voteテーブルのid」と「voteテーブルのvote」にはindexを作成しています。

ウェブサイトから商品に対して投票できるようにしています。

投票は1（最低点）から10（最高点）までになっています。

最高点のみの投票数を合計して、その合計投票数が大きい順に商品をソートできるようにしています。

SUM(IF(v.vote='10',1,0)) AS totalvote がいけないのかなと思っているのですが、他にどのようなクエリの書き方があるでしょうか。

どなたかアドバイス頂けると幸いです。よろしくお願い致します。

回答No.7

chukenkenkou · ベストアンサー

多くの重要なアドバイスが、欠落してしまってますよ？？？

これが最後のアドバイスです。他の回答者さんからのアドバイスもしっかり見直して、何度も同じ指摘を受けたり、情報の小出しはやめましょう。

まず、他の回答者さんからも、繰り返し指摘されていること！

(1)group by idでは、select句にvote列を指定するのは、結果は保証されない。
(2)vote表に、(id,vote)の複数列インデクスを定義する。

create index vote_ix1 on vote(id,vote);

vote列だけのインデクスは、邪魔になるかも知れないので、削除しておいた方がいいかも。

次に、集計結果表を別に作成し、締め日時 or トリガなどで、行数を予め集計した結果を保存。

今のやり方が、どういう問題があるかというと、

(1)毎回、count関数で全idのvote件数を求めている
(2)15万件以上ある表を基準に、(1)をleft join
→毎回、vote=10の書込みが無いidも、全部、検索してしまう
(3)(2)の全件を(1)の降順でソート
→毎回、全件を、「作業ファイルを使ってのソート」が発生する
(4)(3)の上位100件を取り出し
→上位n件を得るには、全件のソートをしないと、順序を決められない

ということ！

↓ これを発想を転換

(1)集計済みの表を定義
id
totalvote・・・id毎のvote=10の件数
dt・・・日時での締めを行うなら、日時の情報が必要でしょう。
(2)create table ・・・selectでは、「インデクスは定義されない！」
(3)(1)に、インデクスを定義しておく！
idをpk
totalvoteは、更新するので、この時点ではインデクスを外しておく

(4)締めの時刻までのid毎のvote=10の行数を、集計し、集計結果を(1)に格納or更新
　行数は、最大でitemの行数。
　行数の最小は、vote=10の書き込みのあるid数

(5)検索前に、(totalvote,id)のインデクスを定義！
　絞込みもしてない状態で、「作業ファイルを使ったソート」が発生するのが大きなオーバーヘッド。
　totalvote列を先頭キーとしたインデクスを定義することで、インデクスを参照し、実ソートを不要にさせる。

(6)集計結果の表を左側で、item表とジョイン
今のSQLでは、
item表を基本にジョイン（vote=10がない物を含め、15万行余りを検索対象にてしまう
　＆
　15万行あまりを、毎回、「作業ファイルを使った全件ソート」を発生させてしまう

　せっかく集計結果の表を作ってるのに、vote表から検索したら意味がないでしょ？
item表を基準にleft joinするというのは、vote=10の書込みが無いid（15万件の中から、上位100件を得たいだけ。殆どが最終的に必要のないid）を全件拾うことになり、どんな意味があるのでしょうか？
vote=10の書込みがあるidを基準(score10表）に、item表からname列やprice列を拾えばいいのでは？

-- --------------------------------------------------------------
-- id毎のvote=10の書き込み数の集計結果を保存 --
-- 集計する場合は、cntを先頭キーとするインデクスは外しておく --
-- itemとジョインする時は、cntの降順で得たいので、インデクスを --
-- 定義 --
-- ------------------------------------------------
drop table if exists score10;
create table score10
(id int primary key
,totalvote int -- id毎にvote=10の行数の集計値！
,dt timestamp -- 締め日時の情報が、必要になるかも知れないので
);

-- MySQL 5.1まで（？）は、drop indexで if existsを使えないみたい
drop index score10idx1 on score10;

-- 集計した結果を
insert into score10(id,totalvote)
select id,count(*) from vote
where vote=10
group by id
;

create index score10idx1 on score10(totalvote,id);

explain
select
s.id
,(select name from item where id=s.id) as iname
,(select price from item where id=s.id) as iprice
,totalvote
from score10 as s
order by totalvote desc
-- ,id desc
limit 0,100
;

お礼

いろいろありがとうございました。
皆さんのアドバイスを見れば分かるはずなのですが
私の理解が足りないばかりに申し訳ありませんでした。
週末にじっくり読み直して試してみたいと思います。
どうもありがとうございました。

回答No.6

chukenkenkou

発想を根本から変えましょう。

今、何をやろうとしているか、自覚してますか？

(1)vote表を検索し、id毎の件数を得る
(2)item表の行数（15万件余り）を左表としてleft join
(3)(1)の降順に全行ソート
(4)(3)の結果から上位n件を得る

さらに、これを一日に、5000回以上やるなんて、馬鹿げています。

こんな方式にしてみては？

(1)vote=10の件数を集計する表を、別に作成。
例）

create table sumtbl
(id int
,vote int -- すべての書き込み件数の保存はオーバーヘッドになるので、vote=10だけ
,cnt int
);
create unique index sumtbl_ix1 on sumtbl(id,vote);

(2)id毎のvote=10の書き込み件数の反映は、次のどちらかを選択
(a)リアルタイムで集計するのでなく、締めの時刻を決めて集計

(b)トリガでvote=10のinsert毎に集計

例）トリガで実装

delimiter // -- 終端記号を変更
create trigger ins_sumtbl after insert on vote
for each row
begin
if new.vote=10 then
insert into sumtbl
values(new.id,new.vote,1)
on duplicate key update
cnt=cnt+1;
end if;
end
//
delimiter ; -- 終端記号を元に戻す

(3)item表の全行（15万件余り）を基本にジョインするのでなく、vote=10の書込みがあったidだけを基本にする。
　この時、検索用に件数を先頭キーにしたインデクスを定義。

-- 検索時に定義
create index sumtbl_ix1 on sumtbl(cnt,id);

実際の検索は、例えば次のようなselect文にする。

select
s.id as sid
,i.id
,i.name
,i.price
,cnt
from sumtbl as s
left join item as i
on s.id=i.id
-- where s.id<=100
order by cnt desc
-- ,s.id desc
limit 0,50
;

-- 検索が終われば、インデクスは削除しておく
drop index sumtbl_ix1 on sumtbl;

補足

合計投票数はリアルタイムじゃなくても構わないので（1日1回夜中のアップデートでも全然OKなので）
chukenkenkouさんがおっしゃるように、vote=10のデータだけ別テーブルとして作成してみようと思いました。

そこで

CREATE TABLE score10 select * from vote where vote ='10';

として

select
i.id AS id,
i.name AS iname,
i.price AS iprice,
v.id AS vid,
v.vote AS vvote,
COUNT(v.id) AS totalvote
from item AS i LEFT JOIN score10 AS v
ON i.id=v.id
GROUP BY i.id
order by totalvote desc
LIMIT 0,100

を試したのですが、データは何もかえってこず、
そのままフリーズしてしまったようになりました。

explainを試したところ以下のようになりました。

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: i
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 152554
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: v
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 17242
Extra:
2 rows in set (0.00 sec)

ERROR:
No query specified

v.voteが10だけのテーブルscore10とjoinしたので、COUNT(v.id) AS totalvote にしたのですが
これが間違っているのでしょうか。

それともvote=10のデータだけ別テーブルとして作成しても意味はないのでしょうか。

回答No.5

nora1962

explain
select
i.id AS id,
i.name AS iname,
i.price AS iprice,
v.id AS vid,
v.vote AS vvote,
SUM(IF(v.vote='10',1,0)) AS totalvote
from item AS i LEFT JOIN vote AS v
ON i.id=v.id
GROUP BY i.id
order by totalvote desc
\G

を実行して

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: i
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: v
type: ref
possible_keys: id
key: id
key_len: 5
ref: test.i.id
rows: 1
Extra:
2 rows in set (0.00 sec)

にならなければ、チューニングの余地はありますが。

このSQLの実行頻度はどれくらいですか？
頻度が少ないなら、「SET SORT_AREA_SIZE =」で一時的にソートエリアを拡大することも検討してください。

補足

遅くなってしまいました。申し訳ありません。
explaneの結果は以下のようになりました。

このSQLの実行頻度は1日5000～10000回ぐらいです。

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: i
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 152554
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: v
type: ref
possible_keys: id
key: id
key_len: 4
ref: test.i.id
rows: 5
Extra:
2 rows in set (0.00 sec)

ERROR:
No query specified

また、いまさらながらなのですが、
サイト構成上の都合でLIMITをかなり使っています。

これが重要とは思ってなかったので書かなかったのですが
もしとても大切なことでしたら申し訳ありません。

以下のように実行した結果が上記のようになりました。

explain
select
i.id AS id,
i.name AS iname,
i.price AS iprice,
v.id AS vid,
v.vote AS vvote,
SUM(IF(v.vote='10',1,0)) AS totalvote
from item AS i LEFT JOIN vote AS v
ON i.id=v.id
GROUP BY i.id
order by totalvote desc
LIMIT 0,100
\G

回答No.4

chukenkenkou

以下のような事項を、提示するようにしてください。

(1)MySQLのバージョン
(2)表の構成列
(3)どの列で行を一意に識別できるのか
(4)表と表はどの列で紐付くのか
(5)具体的に、どのようなインデクスを定義しているか
(6)母体データ例
(7)得たい結果例
(8)EXPLAINの結果

(1)について。
　MySQLのバージョンを、MySQL 4.1、5.0、5.1といったレベルまで、最低限、提示するようにしてください。
大きな機能拡張に加え、一部の仕様変更があります。特に、MySQL 5.1では、「MySQL 5.1.xで、スロークエリの取得方法に関する仕様変更」なんてものあります。

(3)、(4)について。
item表のid列と、vote表のid列で紐付くとして、vote表には、主キーはないのですか？
item表とvote表の関係は、1:nなのですよね？

標準SQL及び殆どのRDBMSでは、group by指定時、select句で指定できるのは、

・group byで指定した列
・max、countなどの集合（集計）関数
・定数

などだけです。

group byで指定した列以外の列を、そのまま指定することはできず、構文エラーになります。

MySQLでは、この部分に拡張仕様があります。標準のインストール時の設定では、構文エラーにならない一方で、「group byでグループ化した結果、その列も一意の値になる場合だけ、結果を保証する。一意にならない場合は、結果を保証しない」としています。その結果、特に初心者は、間違ったSQLで結果が保証されていないのに、それに気付かずに使っている場合が多いです。

例1 正しいSQL

select c1,c2,max(c1),count(*)
from t1 group by c1,c2

例2　標準SQL、殆どのRDBMSでは、間違ったSQL。MySQLでは拡張仕様で使ってもよいSQL

select c1,c2,max(c1) from t1 -- c2をそのまま指定
group by c1

例2では、c1でグループ化し、複数行を1行に集約する訳ですが、この場合、c2の値を決められません。

MySQLでは、c1でグループ化し、c2の値も一意になるなら、この書き方を使ってもよい。一意にならないなら、結果を保証しません。

これはつまり、

select c1,c2,max(c1) from t1
group by c1,c2

と書いた場合と結果が同じ場合のみ、group byでc2を書くのを省略していいという意味で、そうならないなら、結果は保証されないので注意してください。

質問で提示されたSQLでは、select句でvote列を指定していますが、上述の説明の通りで、列の値を決められません。

(5)について
item表のid列は、primary keyなのですね？
vote表のid列は、重複可の単一列のインデクスを定義しているですね？
vote表のvote列にも、重複可の単一列のインデクスを定義しているのですね？
vote表には、これ以外に、primary keyがあるのですか？

vote表は、id列毎にvote=10の行を抽出したい訳だから、(id,vote)という複数列インデクスがあった方が、インデクスを活用できる可能性があります。vote列単独のインデクスは、今回のSQLでは、意味がありません。

(6)、(7)について
SELECT文だけ提示して、「他人にやりたいことを推察して」といった提示の仕方は、SQL自体が不適切なことも多々あり、アドバイスしようとする側に無駄な労力が生じます。「こういう母体データから、こういう結果を得たい。現在、こういうSQLを書いているが、性能が出せない」といったことを具体的に提示してくれると、より適切なアドバイスをより早く得られる可能性が高くなります。

(8)について。
「select文の結果が遅い」といった質問をする場合、explainの結果も提示するようにすれば、より適切なアドバイスをより早く得られる可能性が出てきます。
explainは、select文の先頭に指定するだけです。

例
explain select ・・・ from ・・・

参考URL：

http://dev.mysql.com/doc/refman/5.1/ja/group-by-hidden-fields.html

補足

ご指摘のとおり、私の質問は具体的データが少なく、皆さんの時間を無駄にしてしまったかもしれないと思いました。
申し訳ございません。

分かる範囲ですが現状はこのようになっております。

(1)MySQLのバージョン
5.0.77

(2)表の構成列

[item表]
id int(11) auto_increment PRIMARY
name varchar(255)
price varchar(255)

[vote票]
sid int(11) auto_increment PRIMARY
id int(11) INDEX
vote int(1) INDEX

(3)どの列で行を一意に識別できるのか

恥ずかしい話ですが、質問自体がよく分かりませんでした・・・

ただ、vote表の主キーはsid列になっています

(4)表と表はどの列で紐付くのか

item表のid列と、vote表のid列で紐付いています

(5)具体的に、どのようなインデクスを定義しているか

item表のid列は、primary keyです。

「重複可の単一列のインデクス」がよく分からなかったのですが（すいません）
create index id on vote (id);
create index vote on vote (vote);
でインデクスを作成しました。

item表のid列はprimary keyなのでindexは作成しなくてもいいのかなと思いまして、create indexはしていません。

(6)母体データ例
(7)得たい結果例

No.3の回答者yambejpさんが提示してくだった具体例がまさにやりたいことになります。

(8)EXPLAINの結果

今はEXPLAINを試せる環境ではないためちょっと結果を書くことができませんが、後日には提示できると思います。

みなさんのアドバイスを完全に参考に色々試して見ます。

回答No.3

yambejp

＞PRIMARYでもご指摘のように期待したデータが取れる保証はないのでしょうか。

私の書き方がわかりにくかったのかもしれませんが、若干意味合いがことなります。
v.idはユニークではないですね？であればv.voteもv.idに対して複数あると想定されます
したがってi.idでgroup byするとどのv.voteを表示していいかわかりません。
#2さんが指摘している通りMAXなど何らかの集計用の処理が必要です。

＞それともindexがうまく張られてないからなのか・・・

たぶんそうでしょうね。
本件ですと、itemに対しては、id,name,priceの複合インデックス
voteに対してもid,voteの複合インデックスが必要になります。
(itemのprimaryとindexがかぶるのがあまり推奨されないようですが)

具体的には以下
//itemの作成
CREATE TABLE item(id INT NOT NULL,name VARCHAR(20) NOT NULL,price INT NOT NULL,PRIMARY KEY(id),INDEX(id,name,price));
INSERT INTO item VALUES (1,'a',1000),(2,'b',1100),(3,'c',1200),(4,'d',800),(5,'e',900);

//voteの作成
CREATE TABLE vote(id INT NOT NULL,vote INT NOT NULL,INDEX(id,vote));
INSERT INTO vote VALUES (1,10),(2,1),(3,10),(3,3),(3,10),(3,10),(4,2),(4,5),(5,1),(5,2),(5,6),(6,6),(6,10);

//表示
SELECT i.id AS id,i.name AS iname,i.price AS iprice,v.id AS vid,MAX(v.vote) AS vvote,COUNT(v.id) AS totalvote
FROM item AS i
LEFT JOIN vote AS v ON i.id=v.id
AND v.vote='10'
GROUP BY i.id
ORDER BY totalvote DESC;

結果：
id 　iname　iprice 　vid 　vvote 　totalvote
3　　c　　　1200　　3　　　10　　　3
1　　a　　　1000　　1　　　10　　　1
5　　e　　　 900　　NULL　 NULL　　0
2　　b　　　1100　　NULL　 NULL　　0
4　　d　　　 800　　NULL　 NULL　　0

※ダミーでいれたvoteのvid=6はleft joinの特性上、反映されません

回答No.2

Siegrune

最高点のみの投票数を合計した結果が0のものも出したいんですよね？
で、ひょっとして、このときは、得点の最高を出したいから
v.voteがselect句中にあるのかな？
・・・ならば元の記述ではだめ。
max(v.vote) AS vvote
にしないと何がでてくるかわかりません。

それはさておき、副問い合わせを使ったほうがよっぽど早いと思うけど。
方法１：（mySQLでできるか自信ないですが）
select i.id AS id,i.name AS iname,i.price AS iprice,
(select max(v.vote) from vote v where v.id = i.id) as vvote,
(select count(v.vote) from vote v where v.id = i.id and v.vote='10') as totalvote
from item AS i
GROUP BY i.id order by totalvote desc

方法２：（mySQLでできたはず）
select i.id AS id,i.name AS iname,i.price AS iprice,
v1.vvote as vvote,v2.totalvote as totalvote
from item AS i,
(select v.id,max(v.vote) vvote from vote group by v.id) v1,
(select v.id,count(v.vote) vvote from vote where v.vote='10' group by v.id) v2,
where v.id = v1.id and v.id = v2.id
order by totalvote desc

理由：
item　が100件、voteが10000件として、
元の方法では、100件と10000件を全部結合してから、group byします。
方法１は、100件に対して、1件づつ、2件（maxとsumを求める）selectを実行。
方法２は、10000件を100件にgroup byしてから、100件と結合。
方法１と方法２は、両方できたとして、どちらが早いかはデータ件数や偏り方によりけりと
思われるのでどちらが早いかはわかりません。

回答No.1

yambejp

まず文法が変なところから

＞select i.id AS id,i.name AS iname,i.price AS iprice,v.id AS vid,v.vote AS vvote,SUM(IF(v.vote='10',1,0)) AS totalvote from item AS i LEFT JOIN vote AS v ON i.id=v.id GROUP BY i.id order by totalvote desc;

itemについてidはユニークだという前提で、i.idでgroup byしているなら
v.voteは不定なのでselectで得ることはできません。
MySQLの特質上エラーにはなりませんが、期待したデータがとれる保証はありません。

それを踏まえて・・・vvoteは省略すると
＞最高点のみの投票数を合計して、その合計投票数が大きい順に商品をソート

select i.id AS id,i.name AS iname,i.price AS iprice,v.id AS vid,,COUNT(v.id) AS totalvote from item AS i LEFT JOIN vote AS v ON i.id=v.id and v.vote='10' GROUP BY i.id order by totalvote desc;

みたいな処理の方が軽くなるのでは？
なお、itemとvoteのそれぞれのテーブルには適切なインデックスを貼ってください。

補足

アドバイスありがとうございます。

itemのidはPRIMARYキーになっているのですが、PRIMARYでもご指摘のように期待したデータが取れる保証はないのでしょうか。

もし正確なデータを取りたい場合にはテーブルの設計から見直したほうがいいでしょうか。

また、教えて頂いたクエリーを試してみたのですが速度はほとんど変わりませんでした。

これはご指摘の通り文法がおかしいからなのか、それともindexがうまく張られてないからなのか・・・

indexは「itemテーブルのid」と「voteテーブルのid」と「voteテーブルのvote」に作成しているのですがこれでは不十分なのでしょうか。

質問ばかりで申し訳ありません。