MySQLの複数JOINについて


以下のような商品情報と商品パラメーターを格納したテーブルがあります。

[Itemsテーブル]
・item_code - 001,002...
・item_name - 麦茶,iPhone...
・item_price - 150,30000...
・item_group - A,B...

[paramAテーブル]
・item_code - 001
・volume(内容量) - 2000
・cal(カロリー)- 50
...

[paramBテーブル]
・item_code - 002
・weight(重さ) - 150
・height(高さ) - 100
...

このように商品属性(item_group)によって保存したいデータの種類、カラム数が全く異なるため、商品属性によってパラメータテーブルを分ける設計をしました。
Itemsテーブルは10万件、商品属性は20-30種類程です。

まず、このようなテーブル設計は適当でしょうか?

次に、商品名からパラメータを検索したいとします。この時、どのグループに属する商品が検索されるか分からないものとします。
SQLの書き方として select * from Items join (paramA union paramB union ... paramZ) where Items.item_name = ...
くらいしか思いつかなかったのですが、非常に遅いかつスマートでないSQLだと思います。
この場合の高速かつスマートなSQL文はどのようになるのでしょうか。

以上2点よろしくお願いします。

回答の条件
  • 1人5回まで
  • 登録:
  • 終了:2013/06/11 05:45:05
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

回答5件)

id:taknt No.1

回答回数13539ベストアンサー獲得回数1198

ポイント60pt

まず[Itemsテーブル]のそれぞれの項目に カンマ区切りで データを入れるのは
よくないです。

あと、商品名からパラメータを検索したい場合は、[Itemsテーブル]だけ検索すればよいかと思いますが・・・。

他2件のコメントを見る
id:taknt

で、パラメーターテーブルを検索して どうするんでしょうか?

パラメーターテーブルによって取得できる内容は 違ってきます。
ということは、パラメーターテーブルによって 処理する内容も 変わってくるはずだと思いますが・・・。

また パラメータテーブルって 1レコードだけなんでしょうか?

2013/06/04 06:47:50
id:taknt

全部 UNIONするんでしたら、ひとつのテーブルにまとめてしまったほうが よほど効率的かと思われます。

2013/06/04 09:57:02
id:after_333sec

カンマで区切って1つのフィールドに保存し、APP側で分解しようと考えましたが、パラメータから商品を検索することも多々ありますので、この方法は使えないかなと思いました。

id:dawakaki No.2

回答回数797ベストアンサー獲得回数122

ポイント60pt

unionを使う必要はありません。
下記のようにして外部結合できます。
left joinを明示的に使ってもいいです。

select
  items.item_code, items.item_name, items.item_price,
  paramA.volume, paramA.cal,
  paramA.weight, paramA.height
from items, paramA, paramB
where
  paramA.item_code = items.item_code
  and paramB.item_code = items.item_code
;

select に * は使わないほうが早くなります。
また、paramのテーブル数が多いようだったら、1つのテーブルにまとめる(要素がないものはnull値にしておく)というのも方法です。そのほうが検索は早くなります。

id:holoholobird No.3

回答回数574ベストアンサー獲得回数104

ポイント60pt

item_code,weight,height,volume,cal, ...など、paramA,paramBテーブルを結合したテーブルparamを作成する。
商品属性AのものはBに相当する成分をすべてNULL、逆にBならAに相当する成分をNULLにする。

というような統合テーブルを作成したうえで検索をかける方が、管理も楽になるし検索も早いと思います。

>まず、このようなテーブル設計は適当でしょうか?

同じようなテーブルがparamZまであるのなら、お世辞にも効率がいいとは言えないと思います。

数十万件程度のデータでメモリに余裕があるなら、統合することをお勧めします。

id:Oyama1102 No.4

回答回数3ベストアンサー獲得回数1

ポイント60pt

以下のテーブル構成はいかがでしょうか。

[テーブル一覧]
・商品
・商品属性

[商品テーブルのカラム]
・商品ID ※PK
・商品名
・商品種別 (0:本、1:CD、2:DVD) ※FK
・価格
・数量
・属性1
・属性2
・属性3
 ...
・属性30

[商品属性テーブルのカラム]
・商品種別 ※PK
・属性名1
・属性名2
・属性名3
 ...
・属性名30

商品名や価格のような共通属性は、商品テーブルで管理します。
各商品の任意(固有)な属性は、属性の定義(意味)を商品属性テーブルで管理します。

例えば、商品種別=0:本 の場合は、
・商品種別 = 0
・属性名1 = ISBN
・属性名2 = 著者
・属性名3 = 書籍タイトル
のような形です。

また、商品種別=1:CD であれば、
・商品種別 = 1
・属性名1 = 曲名
・属性名2 = 作曲者
・属性名3 = 作詞者
のような形です。

商品テーブルには、商品種別毎の値のみ格納します。
属性1~30の定義(意味)は、商品属性テーブルで管理します。
※値は商品テーブルの属性カラム、意味は商品属性テーブルの属性名カラムです。

商品名の検索は、商品テーブルを使って検索します。
商品毎の任意属性の検索は、商品属性テーブルを参照し、
検索する属性カラムを特定して検索します。

ISBNであれば、属性1 = "hoge" AND 商品種別 = 0
作曲者であれば、属性2 = "hoge" AND 商品種別 = 1
のような形です。

画面表示する際は、
商品種別を元にカラム名を商品属性テーブルから取得する形になります。

また、商品属性テーブルのデータは、システム起動時に一度だけ読んで、
メモリに保持っておいても良いと思います。

以上、宜しくお願いします。

id:a-kuma3 No.5

回答回数4974ベストアンサー獲得回数2154

ポイント60pt

ぼくなら、こんなふうに作るな。

■Items テーブル

item_codeitem_nameitem_priceattr_group
001麦茶150A
002iPhone30000B



■Groups テーブル

attr_groupattr_code
Avolume
Acal
Bweight
Bheight



■Attrs テーブル

item_codeattr_codevalue
001volume2000
001cal50
002weight150
002height100



で、例えば、名前 = 'iPhone' で属性を探すのはこんな感じ。

select
    Items.item_name, Items.item_price, Attrs.attr_code, Attrs.value
    from Items, Attrs, Groups
    where
        Items.item_name = 'iPhone' and
        Items.attr_group = Groups.attr_group and
        Attrs.item_code = Items.item_code and
        Attrs.attr_code = Groups.attr_code

結果は、こう。

item_nameitem_priceattr_codevalue
iPhone30000weight150
iPhone30000height100

欲しい形とちょっと違うと思うけど、整形はアプリケーションで頑張る(というほどではない)。

属性から探す場合。
例えば、高さ < 200 で名前を探すなら、こんな感じ。

select
    item.item_name, item.item_price, attr.attr_code, attr.value
    from item, attr
    where
        attr.attr_code = 'height' and
        attr.value < 200 and
        item.item_code = attr.item_code

本当なら、コードは数値にしておきたいところ。幾分、速くなることが期待できる。
■Items テーブル

item_codeitem_nameitem_priceattr_group
1麦茶1501001
2iPhone300001002



■GroupDef テーブル

attr_groupgroup_name
1001A
1002B



■Groups テーブル

attr_groupattr_code
1001volume
1001cal
1002weight
1002height



■AttrDef テーブル

attr_codeattr_nameattr_unit
2001volumeml
2002calcal
2003weightg
2004heightcm



■Attrs テーブル

item_codeattr_codevalue
120012000
1200250
22003150
22004100



SQL は、こうなる。

select
    Items.item_name, Items.item_price, AttrDef.attr_name, Attrs.value
    from Items, Attrs, Groups, AttrDef
    where
        Items.item_name = 'iPhone' and
        Items.attr_group = Groups.attr_group and
        Attrs.item_code = Items.item_code and
        Attrs.attr_code = Groups.attr_code and
        Attrs.attr_code = AttrDef.attr_code

属性の名称は SQL で引き当てなくても、アプリケーションで AttrDef テーブルを抱えておいて、そちらで引き当てるというのでも良い。

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

回答リクエストを送信したユーザーはいません