[pgsql-jp: 41218] 掲示板テーブルからのデータ取得について
高田哲郎
takada @ hngrm.com
2012年 9月 20日 (木) 12:26:37 JST
はじめまして、高田と申します。
現在バージョン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の項目に対してそれぞれ設定しておりますが、それ以外には特に設定しておりません。
効率の良い取得方法、もしくは高速にするための有効なインデックス設定案があればご教示いただけませんでしょうか。
よろしくお願い致します。
pgsql-jp メーリングリストの案内