大量の項目を対象に効率的に検索したい

質問

たとえば、こちらに「こだわり条件で絞り込む」という機能があります。

http://www.athome.co.jp/bklist?ITEM=ks&ART=13&AREA=12&KEN=13&DOWN=1

MYSQLでこのような機能を実現する場合、

1項目1カラム、全てにインデックスを設定。

または

該当する項目を1カラムにまとめて格納し（CS,CATV,グルニエ,…）、その中に一致する文字の有無を判断させる。

どのような構造だと効率的に動くようになるのでしょうか？

回答No.3

Siegrune · ベストアンサー

市町村の指定だと、
データ上は１項目しかないはずなので
in ('東京都千代田区',・・・)に選択した項目の内容を入れる。
（データ上にコードで持っていて、in句の中もコードを指定するほうが早いですが。）

こだわり条件のほうでは、例えば、
「収納」を例とすると、
ウォークインクローゼット(0)床下収納(0)
を、
「ウォークインクローゼット、床下収納」と格納し、
検索は一致する文字列を検索するという方法１と、
「11」（1文字目は、ウォークインクローゼット有無で1はあり。2文字目は、床下収納有無で・・・）
と格納し、like演算子を利用して'11'を探す。
ウォークインクローゼットのみチェックなら、'1?'を探す。
とする方法２とあります。
ばらばらに項目として持つ方法３もありますが。

方法１：検索はやや遅い。プログラムも分かりやすいが、「ウォークインクローゼット」をただの「クローゼット」に変える等の名称変更の対応が面倒（データ全件修正必要）。

方法２：検索はやや早い。プログラムは分りにくくなり、追加や削除の対応が面倒。
（ビットマップインデックスが使えるなら、同じような考え方で、検索はより早くなります）

方法３：全件検索に近くなってしまい遅くなる可能性が高い。
（すべての項目に個別にインデックスをつけても、複数の条件を指定されたら、検索時にはどれか１個しかインデックスはつかえないのでほぼ一緒。）
プログラムは分りやすく、追加や削除、変更への対応は簡単。

どれも一長一短なので、要件にあわせて何を優先するかで選んでください。

お礼

ありがとうございます。

>in ('東京都千代田区',・・・)に選択した項目の内容を入れる。
選択するたびにin()の中に追加して検索させるんですね。

>（データ上にコードで持っていて、in句の中もコードを指定するほうが早いですが。）
コードを持っていく、指定するとは何ですか？

方法２は指定する項目が30あれば
00100010100011100000001100100
といったデータを格納するということですね。
方法１と２は管理がすごく大変そうです。

方法３が形としてはいいですが、遅いのが残念です。

Postgresqlなら高速に動き、管理も楽…MYSQLにはムリなのですね

回答No.5

Siegrune

ANO.3ですが。

>（データ上にコードで持っていて、in句の中もコードを指定するほうが早いですが。）
コードを持っていく、指定するとは何ですか？

物件テーブル上は、
101とか102とかコードでもっていて
住所マスタで、
101,'東京都千代田区'
・・・
を別に持つ。
画面は住所マスタから選択肢を表示し、
チェックされたら
in('101','102'・・・)
のようにコードでin句を組み立てるという意図でした。

>方法２は指定する項目が30あれば
>00100010100011100000001100100
>といったデータを格納するということですね。
その通りです。
但し、全部1つにする必要はなく、条件の分類毎に１項目作ればいいと思います。
例えば、先ほどの回答では、「収納」が2桁、「・・・」がn桁・・・という感じです。
何も指定されていなければ、select文のwhere句の中に入れる必要ないので。

方法２の下に
「（ビットマップインデックスが使えるなら、同じような考え方で、検索はより早くなります）」
をつけたのは誤解を招いたかもしれません。
方法３のやり方で、方法２のような検索処理をしてくれるというイメージが近いです。
MySQLにはないようですのであまり書く意味が無かったですが。

お礼

良く分かりました。どうも有難うございました。

回答No.4

yambejp

そんなに深く考えなくても
条件テーブル、物件テーブル、条件＝物件テーブルの３つに分ければ
それなりにはやく、拡張性も高い構成になります
条件テーブルに条件をカテゴライズするカラムを用意しておけばより
検索性があがります

お礼

MYSQLですむ方法があるんですね！

http://www.athome.co.jp/bklist?ITEM=ks&ART=13&AREA=12&KEN=13&DOWN=1
こちらの場合は
価格、間取り、建物面積、土地面積、駅からの徒歩、築年数、情報公開日、アピール、画像、人気のこだわり条件、という検索条件があります。

>条件テーブル、物件テーブル、条件＝物件テーブルの３つに分ければ
こういうことでしょうか。
条件テーブル（人気のこだわり条件）：
条件ごとにbool型のカラムに0か1を入れる。
---------------------------------------
id　|　CS　|　CATV　|　グルニエ　|　…
---------------------------------------
11220|0　　|1　　　|0　　　　　　|　…
11221|1　　|1　　　|1　　　　　　|　…

物件テーブル：
価格、間取り、建物面積、土地面積、駅からの徒歩、築年数、情報公開日、アピール、画像
---------------------------------------
id　|　価格　|　間取り　|　建物面積　|　…
---------------------------------------
11220|2220　　|3LDK　　　|110.98　　|　…
11221|3220　　|1K　　　　|500.00　　|　…

条件＝物件テーブル：
こちらは何の目的でしょうか。

>条件テーブルに条件をカテゴライズするカラムを用意
カテゴリー化したカラムを条件テーブルに追加
---------------------------------------
id　|　CS　|　CATV　|　水周り　|　…
---------------------------------------
11220|0　　|1　　　|0　　　　　　|　…
11221|1　　|1　　　|1　　　　　　|　…
ということですか？

回答No.2

nora1962

そもそも、このような検索ならビットマップインデックスを使用できるPostgresqlのほうが向いているのではないでしょうか。
MySQLを選択せざるを得ない理由があるのなら別ですが。

お礼

http://www.athome.co.jp/bklist?ITEM=ks&ART=13&AREA=12&KEN=13&DOWN=1
いろんな検索、表示順序の変更、何件中何件、1ページ目、とりあえず保存機能、いろいろ機能がありますが、画像は格納する予定はありませんが、こういったものはPostgresqlでやるのが基本ですか？

情報を登録したり更新したりするのは自分一人だけです。

>そもそも、このような検索ならビットマップインデックスを使用できる
>Postgresqlのほうが向いているのではないでしょうか。

MYSQLは機能の豊富さも充実していて、それでありながら一番高速、業界でもシェアNO1、最強のデータベースはMYSQLらしいので、パフォーマンスが落ちなければ、できればMYSQLでやりたいと思っています。

回答No.1

TooManyBugs

全て(出来る限り)の項目をBOOL型にする。

>1項目1カラム、全てにインデックスを設定。
ほとんどの項目は有るか無いかになるのでインデックスを作っても無駄。

>該当する項目を1カラムにまとめて格納し（CS,CATV,グルニエ,…）、その中に一致する文字の有無を判断させる。
最悪の手法でしょう。

お礼

>全て(出来る限り)の項目をBOOL型にする。
それぞれの項目に入れるデータは「0と1」か「trueとfalse」どちらのほうが軽いですか？

>ほとんどの項目は有るか無いかになるのでインデックスを作っても無駄。
これらのカラム全部インデックス付けていないのですか。
格納するデータが1カラムに何種類以上でインデックスは役に立ちますか？