データベースの質問です。

Question

ktoshi

29

28もっと見る

90pt

インターネットウェブ制作

データベースの質問です。

テーブルA,テーブルBがあります。以下の処理を実行させたいのですが、処理速度を早くする良い方法を教えて下さい。
（データベースはpostgreSQL,開発言語はPHP）

１．テーブルAからA.hogeにある値を問い合わせしてA.idを取得します。
２．テーブルBからB.hoge_idに１で取得したA.idを問い合わせしてB.idを取得します。

■テーブルA（レコード件数：約３万件）
列1：id
列2：hoge

■テーブルB（レコード件数：約２０万件）
列1:id
列2:hoge_id

□試したこと
・select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;
・select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');
　上記２点は問い合わせが終了するまでに2秒以上かかります。
・select B.id from B where B.hoge_id IN ('123','456','789');
　A.idを自分で適当に選んで(例えば'123','456','789')実行した場合は、問い合わせが終了するまでに1秒以下でした。
　（１秒以下であれば問題ありません。）

できるだけ1つのSQL文で処理速度を上げたいのでその方法を教えて下さい。
もし、PHPを利用してSQL文を2つに分けて実行させた方が良いのでしたらその方法を教えて下さい。

回答の条件

1人2回まで

登録：2006/08/23 20:53:51
終了：2006/08/24 11:45:06

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

No.1

iketerummo6842006/08/23 22:42:29

10pt

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

でどうでしょうか？

試したことの最初のSQL文が最速ではないかと思っていました。

推測でidが主キーとしてA.hogeに時間がかかっているとしたら

A.hogeに有効なインデックスを付けると改善されます。

select * from A where A.hoge='abc';

↑だけだとどれくらいの応答速度ですか？

回答ありがとうございます。

select * from A where A.hoge='abc';

は、10ミリ秒くらいです。

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

は、明日の朝に試してみます。

2006/08/24 00:19:05

No.2

kurukuru-neko18441552006/08/24 00:12:34

20pt

テーブルに索引を以下のような索引を作成してみて

動作を確認してみてください。

create index a_idx_hoge on a using btree ( hoge );

create unique index b_idex_id on b using btree ( id );

使っている環境が違うので比較になるはどうかは

わかりませんが Indexを付けるだけで

1000倍以上の速度差があります。

適切な索引を指定していますか？

一意なキーにはUNIQUEを指定する。

テーブル作成時にCREATE TABLEでPRIMARY KEY/UNIQUE等の

制約を付ける方方法もある。

======= INDEXによる性能差 =============

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

0.12 ms　 Index有

963 ms Index無

・select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

0.15ms Index有

992 ms Index無

※：explain analyzeでの測定なのでCPU/RAMにより差はあ

　　りますが相対速度はわかると思います。

ありがとうございます。

インデックスは作成しています。（が、念のため明日もう一度確認してみます。）

制約は以下のようになっています。

A.id : PRIMARY

A.hoge : UNIQUE

(B.id,B.hoge_id) : PRIMARY

ちなみに

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

とすると、約3000レコードがヒットします。

2006/08/24 00:45:04

No.4

masal4302006/08/24 10:09:16

40pt

あと、VACUUM FULLとVACUUM ANALYZEを定期的に実行するのをお勧めします

PostgreSQLのマニュアル

AllAboutの解説ページ

回答ありがとうございます。

create index b_idx_hoge_id on b using btree ( hoge_id );

VACUUM FULL;

VACUUM ANALYZE;

を行ったところ、実行時間が40ミリ秒となりとても速くなりました。

また、explanでみたときSeq Scan on b ～と表示されていた部分がなくなりました。

--------------------------------

回答してくださった皆さんありがとうございました。無事解決です。

補足：postgreSQLのバージョンは7.4.6

2006/08/24 11:35:58

kurukuru-neko 2006/08/26 08:02:16

PostGreSQL ベンチマーク＆チューニング
http://www.ipa.go.jp/software/open/forum/development/

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

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

リクエスト送信済

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

kurukuru-neko · Accepted Answer · 2006-08-24T02:09:37+09:00

前のデータは破棄してください[忘れてください]

索引に間違いなので

以下で試してみると

A.id : PRIMARY

A.hoge : UNIQUE

(B.id,B.hoge_id) : PRIMARY

で作成しみると。

create unique index a_idx_id on a using btree ( id );

create unique index a_idx_hoge on a using btree ( hoge );

create unique index b_idx_id_hoge_id on b using btree ( id,hoge_id );

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

55 ms

select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

55 ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

1735 ms

reindex table a;

reindex table b;

vacuum full a;

vacuum full b;

後　explan でみたとき

Seq Scan on b でrowsが全件になっていませんか？

なっている場合遅くて当然なので

indexの見直しが必要と思います。

以下の索引ためしにつけてみてください。

create index b_idx_hoge_id on b using btree ( hoge_id );

追加すると

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

0.15 ms

select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

0.15 ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

1700 ms

※：select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;は１件の場合

===================================================

ためしに200000件のデータを一致させた場合

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

runtime: 1956ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

runtime: 1775ms

>約3000レコードがヒットします

このへんデータ構造が原因ですね。

kurukuru-neko · Accepted Answer · 2006-08-24T02:09:37+09:00

前のデータは破棄してください[忘れてください]

索引に間違いなので

以下で試してみると

A.id : PRIMARY

A.hoge : UNIQUE

(B.id,B.hoge_id) : PRIMARY

で作成しみると。

create unique index a_idx_id on a using btree ( id );

create unique index a_idx_hoge on a using btree ( hoge );

create unique index b_idx_id_hoge_id on b using btree ( id,hoge_id );

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

55 ms

select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

55 ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

1735 ms

reindex table a;

reindex table b;

vacuum full a;

vacuum full b;

後　explan でみたとき

Seq Scan on b でrowsが全件になっていませんか？

なっている場合遅くて当然なので

indexの見直しが必要と思います。

以下の索引ためしにつけてみてください。

create index b_idx_hoge_id on b using btree ( hoge_id );

追加すると

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

0.15 ms

select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

0.15 ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

1700 ms

※：select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;は１件の場合

===================================================

ためしに200000件のデータを一致させた場合

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

runtime: 1956ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

runtime: 1775ms

>約3000レコードがヒットします

このへんデータ構造が原因ですね。

データベースの質問です。

ベストアンサー

kurukuru-neko18441552006/08/24 02:09:37

その他の回答（3件）

iketerummo6842006/08/23 22:42:29

kurukuru-neko18441552006/08/24 00:12:34

kurukuru-neko18441552006/08/24 02:09:37ここでベストアンサー

masal4302006/08/24 10:09:16

コメント（1件)

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