Help required in optimizing query

[Albert Arul prakash Rajendran]

Hi friends,

I am creating a forum using ASP.NET and mysql as db server. My db server is 5.0.18. Below is the data structure of my tables

mysql> desc tbl_topics;
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| topicID | int(11) | NO | PRI | NULL | auto_increment |
| Title | varchar(100) | NO | | | |
| Description | varchar(4000) | NO | | | |
| forumId | int(11) | NO | | | |
| topicBy | int(11) | YES | | NULL | |
| movedto | int(11) | YES | | 0 | |
| topicDate | datetime | NO | | | |
| PostCount | int(11) | YES | | 0 | |
| totalView | int(11) | YES | | 0 | |
| lastPost | int(11) | YES | | NULL | |
| moved | char(1) | YES | | N | |
| blocked | char(1) | YES | | N | |
| Exist | char(1) | YES | | Y | |
+-------------+---------------+------+-----+---------+----------------+


mysql> desc tbl_replies;
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| replyID | int(11) | NO | PRI | NULL | auto_increment |
| topicID | int(11) | YES | | NULL | |
| title | varchar(100) | YES | | NULL | |
| description | varchar(4000) | NO | | | |
| replyBy | int(11) | YES | | NULL | |
| ReplyOn | datetime | NO | | | |
| EditedBy | int(11) | YES | | NULL | |
| EditedOn | datetime | YES | | NULL | |
| EditedReason | varchar(200) | YES | | NULL | |
| Exist | char(1) | YES | | Y | |
| moderated | char(1) | YES | | Y | |
+--------------+---------------+------+-----+---------+----------------+
11 rows in set (0.03 sec)


mysql> desc tbl_users;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| userid | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | YES | UNI | NULL | |
| password | varchar(32) | NO | | | |
| Fname | varchar(50) | NO | | | |
| mname | varchar(50) | YES | | NULL | |
| Lname | varchar(50) | NO | | | |
| email | varchar(150) | NO | | | |
| Country | varchar(2) | YES | | NULL | |
| Activated | char(1) | YES | | N | |
| exist | char(1) | YES | | Y | |
+-----------+--------------+------+-----+---------+----------------+

My requirement is to get the post information which is stored in tbl_replies to be fetched based on the topic Id. Below is the query which i wrote for acomplishing this job

select
a.topicid,a.title as topicTitle,
b.replyid, b.title as posttitle,b.description,b.editedby,
c.username as replyby,b.replyon as timeposted,(
select
username
from
tbl_users
where
userid=b.editedby) as editedby1 ,
b.editedon, b.editedreason, b.moderated
from
tbl_topics a,
tbl_replies b,
tbl_users c
where
a.topicid=b.topicid
and a.topicid=1
and c.userid=b.replyby
and a.exist='Y'
and b.Exist='Y'
order by
b.replyid desc;

This query result will provide me the necessary data which is needed for the page where we display the topic and its replies along with the user details.

This query has three table scan in which tbl_topics might contain comparitievely less amount of data than others. I somehow feel that there is some scope to optimise this query for better performance.

There are primary key index in every table (id fields) and unique constraint in tbl_users table on username. There are no other indexes provided for the tables.

Please let me knwo how can i speed up the query and gain more performance.

Albert
http://www.bepenfriends.com