EXPLAINのtypeがALLのときの対応方法

質問

先日問い合わせたSQLの高速化についてEXPLAINについて教えて頂きました。

そのEXPLAINについて調べながら試していくと、同じphpファイル内で動かしている
いくつかのSQL文に対してEXPLAINのtypeがALLとなっていたので対応すべき
SQLだとは分かったのですが具体的にどのようなSQL文に書き換えればいいか
分からなかったので再度質問しました。

前提としてWORDPRESSでＳＱＬをＰＨＰファイル内で$wpdb->get_results関数を使って
実行しています。

(例1)　条件一致した件数を求めるSQL

SELECT a_mst.a_id
FROM a_mst
WHERE a_mst.a_price >= 100
AND a_mst.a_price <= 200
AND a_mst.a_print_flg = 0

SQLのSELECT にCOUNT等は使わない方がいいようなので主キーのみを出力させて
count関数で件数を求めています。

このＳＱＬ文をEXPLAINでphpMyadminで実行すると

id=1　　　　　　　select_type=SIMPLE　　　table=a_mst
type=ALL　　　　possible_keys=NULL　　　key=NULL
key_len=NULL　　ref=NUL　　　　　　　　　　rows=a_mstに登録してある件数
Extra=Using where

でした。SQL文自体はあるマスタにあるデータから値が１００～２００のもので、フラグが０のものを
出力するというシンプルなSQL文なのでどう改善すればよいのでしょうか？

(例２)　２つのテーブルを結合させて条件一致したデータをそれぞれテーブルから出力

SELECT a_mst.a_name, a_mst.a_price, b_mst.b_data
FROM a_mst, b_mst
WHERE a_mst.a_id = b_mst.b_id
AND a_mst.a_price >= 100
AND a_mst.a_price <= 200
AND a_mst.a_print_flg = 0
ORDER BY a_mst.a_price , a_mst.a_id asc LIMIT 20 OFFSET 0

です。a_mstとb_mstをidで結合させて条件一致したあとにそれぞれの
テーブルデータをSELECTしています。実際のSQL文はSELECT
させている項目数は多いです。

このＳＱＬ文をEXPLAINでphpMyadminで実行すると

＜１行目＞
id=1　　　　　　　select_type=SIMPLE　　　　table=b_mst
type=ALL　　　　possible_keys=PRIMARY　key=NULL
key_len=NULL　　ref=NUL　　　　　　　　　　rows=a_mstに登録してある件数
Extra=Using temporary; Using filesort

＜２行目＞
id=1　　　　　　　　select_type=SIMPLE　　　　table=a_mst
type=eq_ref　　　　possible_keys=PRIMARY　　key=PRIMARY
key_len=NULL　　　ref=b_mst.b_id　　　　　　　rows=1
Extra=Using where

でした。
EXPLAINを使ったチューニングなどしたことないSQL低級者なのでどのようなSQL文にすればtypeがALLの場合や、ExtraからUsing temporary; Using filesortを消すことができるのか分からないので教えて下さる方がいましたらよろしくお願いします。

ちなみに、a_mstとb_mstはphpMyadminにてそれぞれのテーブルの「構造」を見ると
「インデックスサイズ」のところで、それぞれの主キーであるa_idとb_idがPLIMARY で登録させています。編集ボタンを押したら「インデックスを修正する」画面に変わるので一応それぞれインデックス登録はされているのかな？と思います。

回答No.2

yambejp

運用方法を厳密にどうしたいかによりますね

基本的にはプライマリキーはインデックスに含めないのが好ましいので
a_idをプライマリとせずに、別途auto_incrementのプライマリキーを設定してみては？
つまりa_idを含むインデックスをつくるべきだということです。
（a_idが含まれない場合はrangeですが、含まれればtype=indexを得られます）

create table a_mst(pid int not null primary key auto_increment,a_id int not null unique key,a_name varchar(30),a_price int,a_print_flg tinyint);
alter table a_mst add index (a_id ,a_price,a_print_flg);
insert into a_mst(a_id,a_name,a_price,a_print_flg) values(1,'a1',200,0),(2,'a2',150,0),(3,'a3',100,0),(4,'a4',250,0),(5,'a5',50,0),(6,'a6',150,1),(7,'a7',50,1);

//データ抽出
EXPLAIN SELECT a_id,a_price,a_print_flg,b_data
FROM a_mst
INNER JOIN b_mst ON a_id=b_id
WHERE a_price >= 100 AND a_price <= 200 AND a_print_flg = 0;

また、a_nameが集計のキーにならないなら、a_idとの間に別途インデックスを作成して
自分自身を結合するか、別テーブルに正規化してしまうことです。

alter table a_mst add index (a_id ,a_name);
EXPLAIN SELECT a1.a_id,a2.a_name,a1.a_price,a1.a_print_flg,b_data
FROM a_mst as a1
INNER JOIN a_mst as a2 ON a1.a_id=a2.a_id
INNER JOIN b_mst ON a1.a_id=b_id
WHERE a1.a_price >= 100 AND a1.a_price <= 200 AND a1.a_print_flg = 0;

※どうしてもテーブルをわけたくないなら、出力項目全てに対してインデックスを
貼る必要がでてくるかもしれません。しかしあまり大量のインデックスを貼ると
オーバーヘッドが大きく、スピードアップできないかもしれません

お礼

回答ありがとうございます。書いていただいたことを参考にしながらちょっと考えてみます。

回答No.1

yambejp

>(例1)　

これは単純に「a_id,a_price,a_print_flg」の３つでインデックス貼っておくことです

create table a_mst(a_id int not null unique key,a_price int,a_print_flg tinyint);
alter table a_mst add index (a_id,a_price,a_print_flg);
insert into a_mst values(1,200,0),(2,150,0),(3,100,0),(4,250,0),(5,50,0),(6,150,1),(7,50,1);

//検証
EXPLAIN SELECT a_id FROM a_mst WHERE a_price >= 100 AND a_price <= 200 AND a_print_flg = 0;

>(例２)

これは命題が中途半端ですね
「a_name」というカラムがありますがこれは「a_id」ではないのでしょうか？
もし別の項目ならa_nameに対してもインデックスを貼る必要があるかもしれません
もちろんb_mstはb_idとb_dataを利用したインデックスが必要です

補足

早速の回答ありがとうございます。

例１についてですが、「a_price,a_print_flg」の２つでインデックスを追加してみました。「a_id」は主キーなのでphpMyadminですでにPRIMARYでインデックス登録されていましたので。

そうして試すと、ALLはrangeになりました。

例２についてですが、記載不足でした。確かにSELECT文の最初にa_idは必要なので

「a_mst.a_id, a_mst.a_name, a_mst.a_price, b_mst.b_data」の４つです。

例２の回答について再度質問なんですが、私はてっきり条件分岐で使われている項目に対してインデックスを貼る必要があるのではないかと思ったのですが、拝見する限り使われていなくてSELECTにて使われている項目もインデックスを貼る必要があると書いてますが、やはり必要なんでしょうか？

例２のSELECTは実際には14個ほど設定していますがこれらすべてをインデックス登録した方がいいということでしょうか？

また、インデックスの貼り方について例１の場合、私は
a_mstに対しては

[キー名][種別][ユニーク][圧縮][カラム][一意な値の数][照合順序]

PRIMARY：BTREE：はい：いいえ：a_id：1000：A

a_print_flg：BTREE：いいえ：いいえ：a_print_flg：2　：A
　　　　　　　　　　　　　　　　　　a_price　　：100：A

と登録しています。
一行目はテーブル追加したときから存在してます。
この行は削除しない方が良いと思いますが、２行目の分は

a_id：BTREE：いいえ：いいえ：a_id　　　　：100：A
　　　　　　　　　　　　　　　　a_print_flg：100　：A
　　　　　　　　　　　　　　　a_price　　：100：A

という３つで登録した方が良いのでしょうか？