How can I optimize this query?

Question

I've got a bit of a nasty query with several subselects that are really slowing it down. I'm already caching the query, but the results of it changes often and the query results are meant to be shown on a high traffic page.

SELECT user_id, user_id AS uid, (SELECT correct_words 
                                FROM score 
                                WHERE user_id = `uid` 
                                ORDER BY correct_words DESC, incorrect_words ASC 
                                LIMIT 0, 1) AS correct_words,
                                (SELECT incorrect_words 
                                FROM score 
                                WHERE user_id = `uid` 
                                ORDER BY correct_words DESC, incorrect_words ASC 
                                LIMIT 0, 1) AS incorrect_words
FROM score
WHERE user_id > 0
AND DATE(date_tested) = DATE(NOW())
GROUP BY user_id
ORDER BY correct_words DESC,incorrect_words ASC
LIMIT 0,7

The goal of the query is to pick out the top score for users for that day, but only show the highest scoring instance of that user instead of all of their scores (So, for instance, if one user actually had 4 of the top 10 scores for that day, I only want to show that user's top score and remove the rest)

Try as I might, I've yet to replicate the results of this query any other way. Right now its average run time is about 2 seconds, but I'm afraid that might increase greatly as the table gets bigger.

Any thoughts?

Answer 1

The subqueries for correct_words and incorrect_words could be really killing your performance. In the worst case, MySQL has to execute those queries for each row it considers (not each row that it returns!). Rather than using scalar subqueries, consider rewriting your query to use JOIN-variants as appropriate.

Additionally, filtering by DATE(date_tested)=DATE(NOW()) may be preventing MySQL from using an index. I don't believe any of the production versions of MySQL allow function-based indices.

Make sure you have indices on all the columns you filter and order by. MySQL can make use of multi-column indices if the columns filtered or ordered by match your query, e.g. CREATE INDEX score_correct_incorrect_idx ON score ( correct_words DESC, incorrect_words ASC ); would be a candidate index, though MySQL may choose not to use it depending on the execution plan it creates and its estimates of table sizes.

Answer 2

try this:

The subquery basically returns the resultset of all the scores in the right order, and the outer query greps out the first occurence. When grouping in MySQL, columns that are not grouped on return the equivalent to FIRST(column): the value of the first occurence.

SELECT user_id, correct_words, incorrect_words
FROM
( SELECT user_id, correct_words, incorrect_words
  FROM score
  WHERE user_id>0
  AND DATE(date_tested)=DATE(NOW())
  ORDER BY correct_words DESC,incorrect_words ASC
)
GROUP BY user_id
LIMIT 0,7