Hi!
I'm working on a disscusion forum program (PHP + MySQL). When designing the table schema, I tried to follow the normalization principles and now I have 10 tables : Channel, Forum, Topic, Article, ChangeLog, BanList, MailingList, Moderator, Signature and User.
The problem is that I found I have to do lots of joins to get the information I need. For example, this is one of the queries I made to get the information of a Topic :
SELECT Topic.*,
Channel.c_id,
Channel.title,
Channel.visit_priv,
Forum.title,
Forum.visit_priv,
COUNT(*) AS total_article,
MAX(Article.create_time) AS last_post,
User.account,
User.nickname
FROM Channel,Forum,Topic,User
LEFT JOIN Article
ON Topic.t_id = Article.t_id
WHERE Channel.c_id = Forum.c_id AND
Forum.f_id = Topic.f_id AND
Topic.u_id = User.u_id AND
Topic.t_id = $this->t_id
GROUP BY Topic.t_id
Although I've added indexes to my tables and the EXPLAIN result of this query is not too bad, I still wonder that, if I add some level of redundancy to my tables, I can improve the SELECT performance. For example, if I add a "last_post" field to my Topic table, then I won't have to join Article table to find the time of lastest Article in a Topic (EXPLAIN result shown that this is the bottleneck of this query).
I know that if I do this, I'll need to do an extra check and update Topic table every time an Article is posted or deleted. But times of posting / deleting an Article is far less than simply viewing the pages (times of INSERT/DELETE is far less than SELECT), I think this extra effort of keeping the "last_post" field updated should be worth it.
Please share your opinions. Thank you.
I'm working on a disscusion forum program (PHP + MySQL). When designing the table schema, I tried to follow the normalization principles and now I have 10 tables : Channel, Forum, Topic, Article, ChangeLog, BanList, MailingList, Moderator, Signature and User.
The problem is that I found I have to do lots of joins to get the information I need. For example, this is one of the queries I made to get the information of a Topic :
SELECT Topic.*,
Channel.c_id,
Channel.title,
Channel.visit_priv,
Forum.title,
Forum.visit_priv,
COUNT(*) AS total_article,
MAX(Article.create_time) AS last_post,
User.account,
User.nickname
FROM Channel,Forum,Topic,User
LEFT JOIN Article
ON Topic.t_id = Article.t_id
WHERE Channel.c_id = Forum.c_id AND
Forum.f_id = Topic.f_id AND
Topic.u_id = User.u_id AND
Topic.t_id = $this->t_id
GROUP BY Topic.t_id
Although I've added indexes to my tables and the EXPLAIN result of this query is not too bad, I still wonder that, if I add some level of redundancy to my tables, I can improve the SELECT performance. For example, if I add a "last_post" field to my Topic table, then I won't have to join Article table to find the time of lastest Article in a Topic (EXPLAIN result shown that this is the bottleneck of this query).
I know that if I do this, I'll need to do an extra check and update Topic table every time an Article is posted or deleted. But times of posting / deleting an Article is far less than simply viewing the pages (times of INSERT/DELETE is far less than SELECT), I think this extra effort of keeping the "last_post" field updated should be worth it.
Please share your opinions. Thank you.