大量のカラムが検索対象の場合の効率的なDBの構造

質問

phpからmysqlのクエリを作りコンテンツを検索・絞り込みさせようと考えています。
説明するのが苦手なため画像での説明になってしまいますが、

データベースの構造
http://blog-imgs-54.fc2.com/s/k/s/sksfiosjdijf34/20120614-1.jpg

絞り込みページのイメージ
http://blog-imgs-54.fc2.com/s/k/s/sksfiosjdijf34/20120614-2.jpg

種類テーブルだけは「名前」が決まれば「仲間」と「原産地」が決まるので正規化することができましたが、他はできないと思います。
現在所在地テーブルはできそうですが、市によって町名が重なってしまうところがあるので正規化していません。
特徴テーブルは、種類による一般的な特徴よりも実際の特徴を優先しているので、同じ種類でも特徴がそれぞれ違います。

テーブルを分けている理由は、その方が管理しやすいと思ったからです。
特徴テーブル、日付テーブルも同じ理由です。
コメントテーブルのデータは検索しません。
「番号」には主キーを、種類テーブルは「名前」のみ、その他のカラムには全てインデックスをつけます。コメントテーブルはつけません。

そして、絞り込みページでは、
それぞれのカラムでソートさせる機能もあります。
「絞り込み」を押すと選択した条件に全て一致するコンテンツを絞り込みます。

「詳細へ」を押すと詳細のページに入り、
詳細のページでは、そのフィールドの全てのカラムのデータを表示させます。

もっと早く検索でき、効率的な構造にしたいのですが、アドバイスをいただけないでしょうか。

回答No.2

kosukejlampnet · ベストアンサー

（１）文字列にインデックスをつけてもINSERT時のコストの割りに高速化しないので文字列にはインデックスをつけない。
（２）文字列を結合に使わない。
（３）出来るだけINTなどの数値で検索する

DBの定義はこのようにしてみました。

--
-- テーブルの構造 `MST_COLOR`
--
CREATE TABLE IF NOT EXISTS `MST_COLOR` (
`ID_COLOR` int(11) NOT NULL AUTO_INCREMENT,
`COLOR_NAME` varchar(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`ID_COLOR`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='毛色マスター' AUTO_INCREMENT=1 ;
--
-- テーブルの構造 `MST_SPECIES`
--
CREATE TABLE IF NOT EXISTS `MST_SPECIES` (
`ID_SPECIES` int(11) NOT NULL AUTO_INCREMENT COMMENT '犬種ID',
`SPECIES_NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '犬種名',
`COUNTRY` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '原産国',
`COMPANY` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '仲間',
PRIMARY KEY (`ID_SPECIES`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='種類マスタ' AUTO_INCREMENT=1 ;
--
-- テーブルの構造 `TBL_KOBETSU`
--
CREATE TABLE IF NOT EXISTS `TBL_KOBETSU` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '番号',
`ID_SPECIES` int(11) NOT NULL COMMENT '犬種ID',
`PRICE` float NOT NULL COMMENT '価格',
`SEX` tinyint(1) NOT NULL COMMENT '性別',
`HEIGHT` float NOT NULL COMMENT '体高',
`WEIGHT` float NOT NULL COMMENT '体重',
`ID_COLOR` int(11) NOT NULL COMMENT '毛色',
`AVG_AGE` int(2) NOT NULL COMMENT '平均寿命',
`DISEASE` set('無し','有り') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '病気有無',
`PEDIGREE` set('無し','有り') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '血統書有無',
`PREF` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '都道府県',
`CITY` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '市区',
`TOWN` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '町',
`BIRTHDAY` date NOT NULL COMMENT '誕生日',
`UPDATE_DATE` date NOT NULL COMMENT '掲載日',
`COMMENT` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`POINT_CARE` set('1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`POINT_NEIGHBORLY` set('1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`POINT_SECURITY` set('1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`POINT_DEFENSE` set('1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`POINT_COLD` set('1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`POINT_HEAT` set('1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`POINT_PLAY` set('1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`POINT_LIVELY` set('1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`POINT_EXERCISE` set('1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`ID`),
KEY `ID_SPECIES` (`ID_SPECIES`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='個別テーブル' AUTO_INCREMENT=1 ;

お礼

ありがとうございます。凄く参考になりました。
書いて頂いたクエリを図にしました。
http://blog-imgs-54.fc2.com/s/k/s/sksfiosjdijf34/2012-06-16_003547.jpg

検索とソートする場合は下記のようになりますか？

【絞り込み】
SELECT * FROM dog_database WHERE
ID_SPECIES=8 AND
BETWEEN 価格 50.0 and 60.0 AND
性別='メス' AND
BETWEEN 体高 15.0 and 20.0 AND
BETWEEN 体重 4.0 and 5.0 AND
ID_COLOR=3 AND
平均寿命=11 AND
病気='無し' AND
血統書='有り' AND
県='神奈川県' AND
市='横浜市' AND
町='一' AND
BETWEEN 誕生日 2012/1 and 2012/6 AND
BETWEEN 掲載日 1990/1 and 2012/6 AND
手入れ=5 AND
人懐こさ=1 AND
番犬適性=2 AND
防衛能力=3 AND
耐寒能力=5 AND
耐暑能力=1 AND
遊び好き度=2 AND
活発度=4 AND
必要運動量=2
ORDER BY 番号 LIMIT 0,30;

【ソート】
○○○のソートを押された時、指定された検索条件のままでソートしたいので、
上記クエリをそのまま使い、最後のORDER BYのカラム指定だけを○○○に変える。

SELECT * FROM dog_database WHERE
ID_SPECIES=8 AND
---上記クエリと同じ---
ORDER BY ○○○ LIMIT 0,30;

ID_SPECIESだけにインデックスをつけることと、
InnoDB型でなくMyISAM型を選ぶのはなぜでしょうか？

回答No.3

kosukejlampnet

補足について
MyISAMでもInnoDBでもどちらでもよろしいと思います。このようなシンプルな構造のデータベースだからトランザクションがどうしても必要というわけではないだろうということと、MySQLのバージョンの指定がなかったので、MyISAMにしておいたほうが、問題がないだろうということです。

>　ID_SPECIESだけにインデックスをつける
インデックスは付けすぎるとパフォーマンスが低下します。
ID_SPECIESはテーブルを結合するフィールドだからです。
それ以外のフィールドへのインデックスは、実際にデータが入ってから、遅いクエリが発生する場合にチューニングすることで使用するかもしれない選択肢としてとっておきます。
はじめから決め打ちはしません。

お礼

ありがとうございます。

>MyISAMでもInnoDBでもどちらでもよろしいと思います。
>このようなシンプルな構造のデータベースだからトランザクションがどうしても
>必要というわけではないだろうということと、MySQLのバージョンの指定がなかったので、
>MyISAMにしておいたほうが、問題がないだろうということです。

MyISAMがおすすめなんですね。それを基本にします。

>インデックスは付けすぎるとパフォーマンスが低下します。
>ID_SPECIESはテーブルを結合するフィールドだからです。
>それ以外のフィールドへのインデックスは、実際にデータが入ってから、
>遅いクエリが発生する場合にチューニングすることで
>使用するかもしれない選択肢としてとっておきます。
>はじめから決め打ちはしません。

インデックスは更新時に負荷が大きい、なので必要な分だけを適切に付けていくんですね。
付けるときの優先順位は別のテーブルから結合するフィールドからですね。
この状態でもパフォーマンスが遅い時には、
毛の色テーブルが結合するフィールドなので、
今度は毛の色にインデックスを付ければいいということですか。

前回のお例文に画像のURLをはり、きちんと確認したのですが、
なぜかサーバーから画像が消えてしまいました。すみませんでした。
もしよろしければ確認してください。
http://blog-imgs-54.fc2.com/s/k/s/sksfiosjdijf34/201206161934148ba.jpg

回答No.1

yambejp

個別テーブル、現在所在地テーブル、特徴テーブル、日付テーブルは
とくに二重に持ったり頻繁な可変で履歴性が必要なデータでもないし
番号にIDされる個々の犬に紐づくので分ける必要がないのでは？

見づらいというならビューをつかえばよいでしょう

お礼

＞番号にIDされる個々の犬に紐づくので分ける必要がないのでは？
すみません。一番大事な理由を書き忘れていました。
16個までしかインデックスをつけられない、という問題があります。
テーブルを無理やり分けた一番の理由は、これを回避するためです。

＞見づらいというならビューをつかえばよいでしょう
ありがとうございます。試してみます。