MySQLのJOINについて再度質問です。


前回の質問で、テンポラリテーブルを使用する方法を教えていただきました。
テンポラリテーブルは”一時テーブル”だと言うことですが、数万件のデータを扱う場合でも、問題になることはないのでしょうか?(現状では特に問題ありませんが、気になったもので。。)

それから、A,B,Cと三つのテーブルがあったとして、BとCに一致するテーブル(仮にBCとします)をAと結合させたい場合、テンポラリテーブルを使って先にBCの一時テーブルを作成し、その後にAとBCを結合するしかないのでしょうか?(ちなみにAとBCはLEFT JOINでの結合になります)

共にデータ処理のパフォーマンスを重視したいので、速度的に「こうした方が良い」という方法がありましたら、アドバイスいただければと思います。

MySQLのバージョンは4.0.x系でお願いします。

回答の条件
  • 1人2回まで
  • 登録:2007/10/22 19:34:29
  • 終了:2007/10/25 18:14:44

回答(4件)

id:b-wind No.1

b-wind回答回数3344ベストアンサー獲得回数4402007/10/22 22:39:25

ポイント30pt

あまりに範囲が広すぎて答えづらいのですが、


テンポラリテーブルは”一時テーブル”だと言うことですが、数万件のデータを扱う場合でも、問題になることはないのでしょうか?

テンポラリだからといって見た目は普通のテーブルと変わらないので、同様に考えればよい。

つまり数万件単位のレコードがあるテンポラリテーブルが作成される状況は大量の書き込みが一度に発生するので、

ほぼ使い物にならないことが想定される。


A,B,Cと三つのテーブルがあったとして、BとCに一致するテーブル(仮にBCとします)をAと結合させたい場合

ちょっと意味がよくわからないのですが、

A LEFT JOIN B INNER JOIN C

の形は特に問題ないです。


速度的な問題は個々の状況によってさまざまに変わるので一概に言えることはないです。

id:kt26

Aを個人情報とします。2万件ほどあります。Aに対する住所はBに入っており、Cに郵便番号や都道府県コードなどが入っています。(郵政の郵便番号CSVの値です)


Bに入っている住所とCとを結合し、一致するデータを作成する。

その結果とAとを結合したいという意味です。


ですので、数万単位のレコードが存在するので、テンポラリテーブルの場合は「使い物にならない」という状況になると、どうしたらよいものかと思い、悩んでいます。

(現在はBの登録数が少ないので、表示に違和感はありませんが。)


また、JOINの方法ですが、

SELECT * FROM A LEFT JOIN B ON A.id=B.id INNER JOIN C ON B.c_id=C.c_id

とすると、Aの件数に問わず、INNER JOIN後の登録数しか表示されません。


BとCの結合結果に問わず、Aの値を出力したいです。

2007/10/23 05:07:24
id:b-wind No.2

b-wind回答回数3344ベストアンサー獲得回数4402007/10/23 15:09:29

ポイント30pt

Bに入っている住所とCとを結合し、一致するデータを作成する。

その結果とAとを結合したいという意味です。

なにかの検索条件はあるのでは?

検索条件によっては工夫の使用がありますが、全件出力であればどうしようもないです。

この例ではテンポラリテーブルを作る必要はなさそうですが。


BとCの結合結果に問わず、Aの値を出力したいです。

SELECT * FROM B INNER JOIN C USING ( c_id ) RIGHT JOIN A ON A.id = B.id 

でいいんでは?

id:kt26

ありがとうございます。参考にさせていただきます。

2007/10/25 18:12:36
id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542007/10/24 02:06:03

ポイント30pt

テンポラリ・テーブル(一時表)を使う提案を受けたのは、MySQL 4.0では、サブクエリが使えないため、代替手段としてだと推察します。

表A,B,Cで、先にBとCをinner join、その結果とAをleft joinするなら、MySQL 4.0でも、テンポラリ・テーブルを使わずに実現できるように思います。

(MySQL 4.0の環境がなく、実記確認できていません)

複数のinner joinやleft joinを行なう場合、ジョインはその記述順で行なわれます。また、「( )」を使って、ジョインの順序を指定できます。

select * from A
 left join 
   (B inner join C on B.id=C.id)
  on A.id=B.id
id:chuken_kenkou No.4

chuken_kenkou回答回数722ベストアンサー獲得回数542007/10/25 05:45:31

ポイント40pt

BとCは、c_idで結合しているのを見逃していました。

-- 記述形式1
select * from A
 left join 
   (B inner join C on B.c_id=C.c_id)
  on A.id=B.id

-- 記述形式2
select * from A
 left join 
   (B inner join C using(c_id))
  on A.id=B.id

Aのid、Bのid、Cのc_idは、primary keyになっているのでしょうか?

primary keyを指定すると、内部的にインデクスが作成されますが、Bのc_idにも、インデクスを定義しているでしょうか?

データ件数が数千件以上になるなら、インデクスを定義した方が良いと思います。

定義するには、以下のような方法があります。

-- create tableで、uniqueを使って定義
create table B
(id        int   primary key,
 c_id     int,
 jusho   varchar(30),     
 unique(c_id))

-- create indexで定義
create unique index インデクス名 on B(c_id)
id:kt26

はい。primary keyになっています。

調べていくと、「検索が遅い」というのは、どうもインデクスを定義してなかった

事によるかもしれません。

上記はあくまでも例なので、ID以外も結合対象になるのですが、

インデクスを指定すると早くなりました。


また、joinの方法についてもさんこうになりました。ありがとうございます。

2007/10/25 18:13:56
  • id:kt26
    追記です。
    3000レコードあるBテーブルと10000レコードあるCテーブルをINNER JOINで結合してテンポラリテーブルを作ると、処理に物凄く時間がかかります。(ローカルPC上で約10秒)

    と言うことは、b-windさんのおっしゃるように「使い物にならない」と言うことですね・・。

    ただ、BとCを結合させた結果をAと結合させる必要はあるので、どのような構造にしたらいいのか迷います。(Cで必要となるフィールドをBの方にはじめから用意すればいいのでしょうが…)

    ご存じの方はご回答・アドバイスいただければ助かります。
  • id:kn1967
    テーブルBにも郵便番号や都道府県コードといったフィールドを用意してしまって
    新規登録時や修正時のみ、テーブルCからデータを持ってくるようにしたほうが効率的ですが・・・。

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

トラックバック

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

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

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