松永です。

あるテーブルに紐付く検索条件項目が非常に多くある場合のテーブル設計/検索の仕方で悩んでいます。

普通に横持ちの場合、
create table a (
	a_id int(10) not null,
	fk_id int(10) not null,
	name varchar(255) not null,
	cond1 int(2),
	cond2 int(2),
	cond3 int(2),
	....
	primary key (a_id),
	index (cond1, cond2, cond3,...)
)
のような感じで、条件が60以上並び、また条件が追加などされる可能性があるテーブルがあります。このテーブルは他のテーブルをjoinして使います。
検索軸としては、cond1 ＞ 10 and cond3 = 20のように、必ず全ての条件カラムを使うとは限りません。

そこで、条件を下記のように縦持ちにしてみました。
また、条件はコードのようになっているので、cond1自体を001、cond2自体を002等とし、そのcond自体が持つコードを後ろに連結し、cond1が10の場合を00110(正しくは110)というintで表わすようにしました。

create table a (
	a_id int(10) not null,
	o_id int(10) not null,
	name varchar(255) not null,
	primary key (a_id)
)

create table cond (
	a_id int(10) not null,
	cond int(5) not null,
	primary key (a_id, cond)
)

この状態で、他のtable(other)をjoinしつつ、cond1(001)が10あるいは20、かつcond2(002)が70あるいは80あるいは90の条件を見たすrecordが欲しい場合、
select a_id, o.val
from
	a
inner join cond c1 using(a_id)
inner join cond c2 using(a_id)
inner join other o using(o_id)
where 
	c1.cond between 00110 and 00120
	and c2.cond between 00270 and 00290;
というsqlを実行してみたのですが、結構重い状態です。
condにはテストデータが135万件程度入ってる状態で、 初回実行(キャッシュに乗っていないと思われる状態)で、8秒近く掛る場合もある為、ちょっと性能的に問題外な状態になってしまってます。

本番の場合はcondは1000万件になる予定です。また、このqueryは発行される数は、5q/s程度考えられます。

これ、なにか考え方が間違っているのでしょうか。
なにか指摘頂けるとありがたいです。


下記に上記内容を纏めたsqlを添付します。
---------------------------------------------
create table other (
	o_id int(10) not null,
	val varchar(255) not null,
	primary key(o_id)
);
create table a (
	a_id int(10) not null,
 	o_id int(10) not null,
  name varchar(255) not null,
	primary key (a_id),
	index (a_id, o_id)
);
-- 条件テーブル
create table cond (
	a_id int(10) not null,
	cond int(5) not null,
	primary key (a_id, cond)
);
insert into other (o_id, val) values(1,'1'), (2,'2'), (3,'3');
insert into a (a_id, o_id, name) values (1, 1, '1'), (2, 1, '2'), (3,2,'3'),(4,2,'4');
insert into cond (a_id, cond) values(1, 00110), (2, 00120), (1, 00201), (2, 00270), (2,00230), (3,00290);
	
-- explain
select a_id, o.val
from
	a
inner join cond c1 using(a_id)
inner join cond c2 using(a_id)
inner join other o using(o_id)
where 
	c1.cond between 00110 and 00120
	and c2.cond between 00270 and 00290;




-- 
MATSUNAGA Ichiro
e-mail: vikke.bsd@xxxxxxxxxx
/.j: http://slashdot.jp/~vikke/
last.fm: http://www.lastfm.jp/user/vikke_bsd/
twitter: http://twitter.com/vikke

Just remember - when you think all is lost, the future remains.
-- Robert H. Goddard

GPG fingerprint = DCEF C86E 2930 45D0 0941  E977 4DCE A95F 3914 4BED