MySQL Forums
Forum List  »  Optimizer & Parser

Help required in optimizing query
Posted by: Albert Arul prakash Rajendran
Date: February 08, 2007 09:43PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Help required in optimizing query
3178
February 08, 2007 09:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.