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 (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:
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;
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