MySQLでUNIONとJOINの使い方について質問です。


$sql="SELECT * FROM address RIGHT JOIN(
SELECT * FROM a_table
UNION ALL
SELECT * FROM b_table
UNION ALL
SELECT * FROM c_table) AS table_set
ON address.address_id=table_set.address_id
";

としています。サブクエリで3つのテーブルをUNIONで結合し、それをaddressテーブルと結合しています。

a_table,b_table,c_tableをUNIONした時の合計は6000レコードあります。(table_setとします)
addressテーブルには10000レコードあります。

table_setとaddressをRIGHT JOINで結合するわけですから、結果は6000レコードになるかと思ったのですが、
mysql_num_rowsで調べたら、約150万レコードになりました。(そして表示するまで凄い時間がかかります)

サブクエリを止めてテンポラリテーブルにしても同じです。

SQLの書き方が間違っていると思うのですが、どうすればいいのでしょうか?
ご存じの方は、アドバイスいただければと思います。

ちなみに、MySQL4.1.22を使っています。

回答の条件
  • 1人2回まで
  • 登録:2007/12/24 02:30:14
  • 終了:2007/12/31 02:35:02

回答(3件)

id:wnagata No.1

wnagata回答回数170ベストアンサー獲得回数182007/12/24 11:11:34

ポイント27pt

同じtable_set.address_idのレコードがa_table,b_table,c_tableをUNIONしたテーブルセットの中に複数存在すれば、address の1レコードに対して、複数行返されるので、結果は6000レコードよりも多くなります。

SELECT * FROM a_table

UNION ALL

SELECT * FROM b_table

UNION ALL

SELECT * FROM c_table

の部分で、各行のaddress_idが一意となるように条件を指定する必要があります。

http://www.bnote.net/mysql/appendix/left_join.shtml

id:kt26

回答ありがとうございます。よくよくテーブル構成を分析していると、

address_idに重複値があるので、結合した時に複数行が返されるのだと思います。


それではと、GROUPしたのですが、UNIONで結合したtable_setをGROUPすると

IDが被ってしまい、6000レコードではなく、2000レコードになってしまいました。


UNIONするのでIDも被ってしまうのはしかたないかもしれませんが、当初の目的である「1テーブル2000レコードのデータを結合して6000レコードにし、6000レコードに対してJOINをする」と言うことに反してしまいます。


UNIONで結合した各行を一意にするのってできませんかね?

2007/12/24 12:07:30
id:b-wind No.2

b-wind回答回数3344ベストアンサー獲得回数4402007/12/24 16:33:34

ポイント27pt
SELECT * FROM address RIGHT JOIN a_table ON address.address_id=a_table.address_id
UNION ALL
SELECT * FROM address RIGHT JOIN b_table ON address.address_id=b_table.address_id
UNION ALL
SELECT * FROM address RIGHT JOIN c_table ON address.address_id=c_table.address_id

テーブル構造がよくわからんけど、これでいいような気がする。

id:kt26

それをすると、件数が件数だけに、アクセス時間が凄くかかります。(10秒以上)

出来るだけチューニングを行いましたが、アクセス速度の改善は特に変わりませんでした。


それは1万レコードあるaddressテーブルを3回結合しているからだと思い、質問のようなSQLを書きました。


ちなみに、おっしゃる方法をしても質問のようなサブクエリをしてもアクセス時間はほとんど変わりません。

2007/12/24 21:08:39
id:matsu-boolean No.3

matsu-boolean回答回数43ベストアンサー獲得回数72007/12/25 01:57:54

ポイント26pt

2の回答に対して…

結果がこれでよいなら、ここからチューニングしていくしかない気がします。

さしあたり、addressテーブルのaddress_idにインデックスはありますか?。

ちゃんとインデックスが効いているなら、数千~10000件程度のデータでこのクエリーが10秒以上というのは遅すぎな気がします。

SQLチューニングツールとしてこんなものを作っています。


A5:SQL Mk-2

http://www.wind.sannet.ne.jp/m_matsu/developer/a5m2/

http://www.wind.sannet.ne.jp/m_matsu/developer/a5m2/help/SQLEdit...

id:kt26

ちゃんとインデックスを指定しているので、効いているとは思います。動作もローカルPC上で行っているので、極端にスペックが低いとか回線が遅いとか言う問題でもありません。


しかし、それでも質問のSQLや回答2のSQLを実行すると、表示されるのに10秒以上はかかる現状であります。


教えていただいたURLは、是非参考にさせていただきます。ありがとうございました。


[追記]

教えていただいたツールを使いながらテストをしていたところ、"SELECT * "の箇所がまずかったようです。回答2で書いていただいたSQL文と*を止めてフィールド指定したところ、2秒まで短縮する事が出来ました。良いツールを教えていただき、ありがとうございました。

2007/12/25 03:18:57
  • id:bayan
    > UNIONで結合した各行を一意にするのってできませんかね?

    UNION ALLではなく、単に UNION としたら重複行はまとまりませんか?
  • id:kt26
    >UNION ALLではなく、単に UNION としたら重複行はまとまりませんか?

    まとまりません。個々のテーブルは一意ですから、UNIONで結合しても6000レコードとなります。

    ただ、address_idがUNIONする事に事によって被ってしまうので、それで予期せぬ結果になっているのかも知れません。
  • id:b-wind
    >それをすると、件数が件数だけに、アクセス時間が凄くかかります。(10秒以上)
    必要用件がそうなんだから仕方なかろ。
    テーブル構造か用件に問題があるように思うが、もうちょっと詳細な情報は出せない?
  • id:kt26
    テーブル構造ですが、
    a_tableのフィールドが
    [a_table_id][name][address_id]
    インデックスはaddress_idにあり、a_table_idがプライマリーです。


    です。(b_table,c_tableも構成は同じ)


    addressテーブルは
    [address_id][zip][pref][addr1][addr2]

    となっています。入っている値は
    [1][640941][北海道][札幌市中央区][旭が丘]

    と言う風になっている(郵政の郵便番号CSVを使っています)

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

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

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

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません