PostgreSQL & PHP Tutorials - Database Design Issues - Final Query

PostgreSQL »  Database Design Issues
Random Stuff »  Database Design Issues
Forum Project »  Database Design Issues

Posted By Chris Smith Posted on 20 Apr 2006, 08:54 AM
Viewing page 5 of 5
« | Back | Next | »

Now that we're updating the information instead of using a subquery and left join, we end up with a much simpler query:

SELECT forumid, forumtitle, forumdescription, forumnode, createdate, threadcount, postcount FROM myforum_forums ORDER BY forumnode ASC;

We might get a slight performance decrease when adding threads or posts to a forum since the database has to process and run the trigger (which in turn updates the other database table), but since most of the time we're only selecting records, this is totally acceptable and gives us a huge performance increase in the vast majority of cases.

We don't lose anything with this particular approach, or make the database contain a lot of redundant data or make it difficult to update, we get the database to keep itself up to date.

Of course, depending on your application, database design and the types of queries you are running, there will be other things to consider. If you find performance becoming an issue, maybe an approach like this will save the day!
Viewing page 5 of 5
« | Back | Next | »



Avg Rating: 3
Vote Count: 19


              
  New Reply

You've missed an important point. Consider the following:

---
create table forums (
forumid int primary key
);

create table threads (
threadid int primary key,
forumid int references forums
);

create table posts (
postid int primary key,
threadid int
);

insert into forums values(1);
insert into forums values(2);

insert into threads values(1, 1);
insert into threads values(2, 1);

insert into posts values(1, 1);
insert into posts values(2, 1);

insert into posts values(3, 2);
insert into posts values(4, 2);
insert into posts values(5, 2);
insert into posts values(6, 2);

select f.forumid, count(distinct t.threadid), count(distinct p.postid) from forums f
left outer join threads t on(f.forumid = t.forumid) left outer join
posts p on(t.threadid = p.threadid) group by 1;

---

This is a correct design and simpler query. However, this is not the best way to do what you're trying to achieve in PostgreSQL. Instead, you should use rules and triggers to maintain a forum, thread and post count in a count cache table. This will give you O(n) count performance and can be done without modifying your application code. Just create insert and delete triggers/rules to manage the counts when posts/threads are added and removed. 		Gavin Sherry 23 Apr 2006 Reply
`-- I thought that's what I was trying to demonstrate.. or do you mean having a separate count table instead of including it in the forum/thread table ? Chris Smith 24 Apr 2006 Reply

Want to post a comment? Fill in the details below.
Your Name  : 
Your Email  : 
Your Website  : 
Spam Check! Please answer this question  : 7 + 5 =
Comment