Need Help Optimizing A Query

YeahWhat · #1 (**permalink**) 04-23-06, 04:19

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:

Code:

Quotes
ID	Author	Quote	...	...	...
1	Bob	Quote1
2	Joe	Quote2
3	Jack	Quote3
4	Jane	Quote4
5	Mary	Quote5
6	Joe	Quote6


Comments
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:

Code:

SELECT	q.id,
	q.author,
	q.quote,
	q.board,
	q.url,
	q.Date,
	q.Nomination,
	q.TotalVotes,
	q.NumberOfVotes,
	count(c.postid) as NumberOfComments,
	Max(c.CommentID) as Bob FROM Quotes q LEFT JOIN Comments c ON (q.id = c.postid )
WHERE q.Approved = 1 GROUP BY q.id 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:

Code:

Output
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:

Code:

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