人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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を使っています。

●質問者: kt26
●カテゴリ:ウェブ制作
✍キーワード:ALL as MySQL ON SELECT
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● wnagata
●27ポイント

同じ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

◎質問者からの返答

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

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


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

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


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


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


2 ● b-wind
●27ポイント
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

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

◎質問者からの返答

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

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


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


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


3 ● matsu-boolean
●26ポイント

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...

◎質問者からの返答

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


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


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


[追記]

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ