[pgsql-jp: 41219] Re: 掲示板テーブルからのデータ取得について

[H.Nakai]

中井と申します。

私の理解が間違っているかもしれませんが、私なら「通常の投稿」の
response_toはNULLにせずにmessage_idと同じ番号を入れるようにします。
そうすれば、response_toの同じレコードの中で最大のmessage_idを
検索すればいいことになります。検索条件が簡単なだけ速くなりそうな
気がします。
外していたらごめんなさい。

(2012/09/20 12:26), 高田哲郎 wrote:
> はじめまして、高田と申します。
> 
> 現在バージョン8.1で掲示板を稼働させているのですが、効率良くデータを取得する方法について教えていただきたいことがあります。
> テーブル構成は下記のような形となっております。
> 
> テーブル名：bbs
> 項目：message_id（連番のメッセージID）,response_to（返信先のメッセージID）,name（投稿者名）,title（件名）,message（本文）,created_at（投稿日時）
> 
> 上記のようなかたちで、通常の投稿はresponse_toがNULL、メッセージID「12」に対する返信投稿であればresponse_toに「12」が入ります。
> このテーブルからresponse_toがNULLのデータ（返信ではない投稿）の一覧を取得するのですが、その際の取得順は、返信がなければ投稿日時、返信があれば最新の返信日時、が新しい順としています。
> 現状は下記のようなクエリにより取得しています。
> ※投稿データに加えて返信の件数、最新の返信日時も併せて取得しています
> 
> SELECT
> 	bbs.*,
> 	( select count(*) from bbs s1 where response_to = bbs.message_id ) as count,
> 	case when j2.created_at is not null then j2.created_at else
> bbs.created_at end as latest_time
> FROM
> 	bbs
> LEFT JOIN
> 	(
> 		SELECT
> 			r1.response_to,r1.created_at
> 		FROM bbs r1
> 		LEFT JOIN bbs r2 ON r1.message_id < r2.message_id and r1.response_to
> is not null and r2.response_to is not null and r1.response_to =
> r2.response_to
> 		WHERE r1.response_to is not null
> 		GROUP BY r1.response_to,r1.created_at
> 		HAVING count(r2.*) = 0
> 	) j2 ON bbs.message_id=j2.response_to
> WHERE bbs.response_to is null
> ORDER BY latest_time DESC
> 
> 
> 一件の投稿に対する返信の中で最も新しい（メッセージIDの値が大きい）ものの日時を取得する箇所で、かなり処理が重くなってしまいます。
> 上記のクエリでは最新の返信日時もSELECTしていますが、その順に並べられれば特に取得する必要はありません。
> 
> インデックスはmessage_id（主キー）とresponse_toの項目に対してそれぞれ設定しておりますが、それ以外には特に設定しておりません。
> 
> 効率の良い取得方法、もしくは高速にするための有効なインデックス設定案があればご教示いただけませんでしょうか。
> よろしくお願い致します。
> 


-- 
-=-=-=-=  SHINKO ELECTRIC INDUSTRIES CO., LTD.           =-=-=-=-
=-=-=-=-    Research & Development Div.                  -=-=-=-=
-=-=-=-=      Designing Technology Development Dept.     =-=-=-=-
=-=-=-=-  Name:Hisakazu Nakai          TEL:026-263-3922  -=-=-=-=
-=-=-=-=  Mail:nakai ＠ shinko.co.jp      FAX:026-263-4562  =-=-=-=-