MySQLの抽出について

質問

MySQLの抽出時間を短縮できないため困っております

productテーブル
product_id,test_id
86009,'10701,8545'
84334,'12610,9505,13326,9024,1020747,6787,3737,5266'
85078,'1001182,1002129,1003064'
84082,'24723,23417,1002357,1000683,1002258,1003501,25426'
85072,'1008167,1007194,1006309,5240,1009347,30212'
85071,'28079,1007881,1000733,1002367,27836,25141,29863'
86014,'23988,17802,26440,1005928,22668,28372,28135'
83762,'1010127,1010436,1011554,1013949,1014307'

test_masterテーブル
id,initial_kana
10701,'あ'
21122,'あ'
1002129,'あ'
1002357,'い'
2291,'い'
29863,'い'
23988,'う'
1010436,'え'
22299,'え'

上記の２つのテーブルがあります。
内容は下記のようになります。

productテーブルの
product_id：一意のID
test_id：test_masterテーブルの一意のID(test_master.id)のカンマ区切りで接続した文字列
実際のレコード数：86,688件

test_masterテーブルの
id：一意のID
initial_kana：全角一文字のみ
実際のレコード数：14,602件

【やりたいこと】
（１）test_masterテーブルのtest_master.initial_kanaカラムに対応する文字（例えば'あ'）に対応するtest_master.idを取得する
（２）（１）で取得したtest_master.id群を元に、productテーブルのtest_idに
　　　該当する（product.test_id文字列の中にtest_master.idが入っているもの）もののproduct.product_idを取得する
ということをしたいのです。
（３）例えば、test_master.initial_kana='あ'を取得したい場合、productテーブルのproduct.product_id=86009,85078が取得される

私の作ったSQLは下記のものになります
limitとoffsetに関しては、都合上消すことはできません。
・test_masterテーブルから、test_master.initial_kana='あ'のものを抽出
・productテーブルから、product.test_idがNULLでないものを抽出
・上記二つから、FIND_IN_SETで、test_master.idに該当するproduct.test_idの文字列の中から抽出

---------------------------------------------------------
select p.product_id as product_id,
am.id as id
from ( select id
from test_master
where initial_kana = 'あ' ) as am,
( select product_id,test_id
from product
where test_id is not null
) p
WHERE ( FIND_IN_SET(am.id, p.test_id)!=0 )
group by am.id
limit 20
offset 0
---------------------------------------------------------

このSQLを実行すると件数が多いためか
30秒ほど掛かってしまい実用に耐えられない状況です。

すみませんが、SQLに詳しい方がいらっしゃいましたら
ご教授をお願いできますでしょうか。
検索が5秒くらいまで短縮できれば万々歳なのですが…

よろしくお願いいたします

回答No.3

nora1962

test_masterテーブルの「initial_kana」にINDEXはありますか？
なかったら付けてください。
後、インラインビューの「where test_id is not null」の必要性
が分かりません。
「test_id」にINDEXがあり、しかも内容がほとんどNULLならば、
この段階で絞込むこともあるかもしれませんが、そうでないなら、
FIND_IN_SET関数の判定と二重チェックになると思います。

selectp.product_id as product_id, am.id as id
from test_master as am join product as p
on am.initial_kana = 'あ'
and FIND_IN_SET(am.id, p.test_id)<>0
group by am.id
limit 20
offset 0

で試してみてください。

回答No.2

mitoneko

なんだか、すごく、違和感を感じるＳＱＬ文です。
　処理時間も、結構無駄っぽい気がする。

　とりあえず、等価なＳＱＬ文を作ってみると、
select p.product_id as product_id,
am.id as id
from product p join test_master am on FIND_IN_SET(am.id, p.test_id)!=0
where am.initial_kana = 'あ'
group by am.id
limit 20
offset 0

　もし、joinで、文句を言われるようなら、

select p.product_id as product_id,
am.id as id
from product p , test_master am
where am.initial_kana = 'あ' and FIND_IN_SET(am.id, p.test_id)!=0
group by am.id
limit 20
offset 0

productテーブルのtest_idが、nullではないという条件は、多分不要です。というのは、test_idがnullだとすると、FIND_IN_SETが発見の結果を返してくることはないはずだからです。

　出来れば、test_masterのinitial_kanaには、インデックスをつけておきましょう。

　product_idと、test_idの組が一意であるという仮定が、ＯＫであれば、group byは処理を遅くするだけで意味が無いと思います。(意味を補足すると、productテーブルのproduct_idに重複がなく、且つ、test_idにも行内での重複がないという事です。）でも、limitがついていることから、order by am.idが必要になりそうなので、処理時間にはあまり影響しなさそうかな。

　本来なら、test_idが、カンマ区切りリストではなく、素直に、一つのｉｄが入った数値カラムだとすれば、idにインデックスが作成されていれば、爆速なはずです。５秒でも遅いといわれそう。
　というのを踏まえて、テンポラリーテーブルを作成することも考慮に入れて良いかもしれません。
　処理の一環として、product2というテーブルをproductから作成します。
　product2のテーブルは、product_id(int)とtest_id(int)からなり、productテーブルのtest_idを展開した物です。このテーブルの主キーはproduct_id,test_idとなります。（product_idは、test_idの個数分だけ重複します。）
　product2テーブルに、別途test_idのインデックスもつけておきましょう。

select p.product_id as product_id,
am.id as id
from product2 p join test_master am on am.id=p.test_id
where am.initial_kana = 'あ'
group by am.id
limit 20
offset 0

　この構造なら、多分、selectの処理にはほとんど時間を要しません。
　問題は、テンポラリーテーブルを作成する時間勝負ですが、多分、トータルではテンポラリーを作る方が早いような気がします。

回答No.1

yambejp

テーブルの形式は確定ですか？
これではどうやってもスピードがでないと思いますが・・・

productテーブル作り変え
create table product(product_id int ,test_id int,unique (product_id,test_id));
insert into product values(86009,10701),(86009,8545),(84334,12610),(84334,9505),(84334,13326),(84334,9024),(84334,1020747),(84334,6787),(84334,3737),(84334,5266),(85078,1001182),(85078,1002129),(85078,1003064),(84082,24723),(84082,23417),(84082,1002357),(84082,1000683),(84082,1002258),(84082,1003501),(84082,25426),(85072,1008167),(85072,1007194),(85072,1006309),(85072,5240),(85072,1009347),(85072,30212),(85071,28079),(85071,1007881),(85071,1000733),(85071,1002367),(85071,27836),(85071,25141),(85071,29863),(86014,23988),(86014,17802),(86014,26440),(86014,1005928),(86014,22668),(86014,28372),(86014,28135),(83762,1010127),(83762,1010436),(83762,1011554),(83762,1013949),(83762,1014307);
create table test_master(id int,initial_kana varchar(10),index(id,initial_kana));
insert into test_master values(10701,'あ'),(21122,'あ'),(1002129,'あ'),(1002357,'い'),(2291,'い'),(29863,'い'),(23988,'う'),(1010436,'え'),(22299,'え');

test_masterテーブル
create table test_master(id int,initial_kana varchar(10),index(id,initial_kana));
insert into test_master values(10701,'あ'),(21122,'あ'),(1002129,'あ'),(1002357,'い'),(2291,'い'),(29863,'い'),(23988,'う'),(1010436,'え'),(22299,'え');

抽出
select product_id from product as p inner join test_master as tm on tm.id=p.test_id and tm.initial_kana='あ'