Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005

    Unanswered: Need Help Optimizing A Query

    For the past year, I've been running a site, and since its become more and more popular, one of my pages is beginning to slow down.

    In a nutshell, my site is like a blog, where topics are posted and people can leave comments on topics if they like. There are two tables, Quotes, and Comments; the Quotes table holds the topics, and the Comments table holds all users comments.

    The tables are set up like this:
    ID	Author	Quote	...	...	...
    1	Bob	Quote1
    2	Joe	Quote2
    3	Jack	Quote3
    4	Jane	Quote4
    5	Mary	Quote5
    6	Joe	Quote6
    CommentID	PostID	Author		Comment
    1		1	Person1		Comment1
    2		6	Person2		Comment2
    3		5	Person3		Comment3
    4		1	Person4		Comment4
    5		6	Person5		Comment5
    6		6	Person6		Comment6
    7		4	Person7		Comment7	
    8		2	Person8		Comment8
    9		2	Person9		Comment9
    10		3	Person10	Comment10
    Here is the query I want to optimize:
    	count(c.postid) as NumberOfComments,
    	Max(c.CommentID) as Bob FROM Quotes q LEFT JOIN Comments c ON ( = c.postid )
    WHERE q.Approved = 1 GROUP BY ASC ORDER BY Bob DESC Limit 25
    Explanation of this query: The field q.Approved is just a True/False field that I use, because I have to approve all quotes before they are posted on my site, otherwise users might see spam or objectionable content (this field is set to 0 by default until I change it to 1). I define a field called Bob, which selects the Maximum CommentID from the Comments table, which is used to put recent comments in descending order. The output looks like this:

    ID	Author	Quote	NumberOfComments	Bob (max CommentID)	...	...
    3	Jack	Quote3	1			10
    2	Joe	Quote2	2			9
    4	Jane	Quote4	1			7
    6	Joe	Quote6	3			6
    1	Bob	Quote1	2			4
    5	Mary	Quote5	1			3
    The SQL works beautifully, except that it performs a full table scan and take 6+ seconds to execute. Here is the information Explain reveals about my query:
    table	type	possible_keys	key	key_len		ref	rows	extra
    q	ref	Approved	Approve	2		const	3374	Using where;
    									Using temporary;
    									Using filesort
    c	ref	PostID		PostID	4		q.ID	4
    Ouch. The obvious problem is that I'm calling up 3374 rows, when I only need 25 of them. The obvious solution is to set a key so MySQL can search faster... but I don't have the faintest clue which field should have a key on it.

    If anyone has a suggestion on how to optimize my query, I'd really appreciate it
    Last edited by YeahWhat; 04-23-06 at 04:26.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by Yahweh
    but I don't have the faintest clue which field should have a key on it.
    id should be declared primary key in the quotes table, and postid should get an index in the comments table | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts