Mysql – Why does adding LIMIT to the query make it crawl

group bylimitsMySQLoptimization

Simple query:

select sum(score) total,name,gender,dob,country 
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by scores.user_id having sum(score)>=1000 order by sum(score) desc limit 50

So trying to get a list of accumulated scores for Jan 2012, order them by scores descending and paginate them.

Without limit: slowish but OK: searches 69348 rows. (Be nice to work out how to avoid the temporary table but I can't). Explain says: 

1, 'SIMPLE', 'scores', 'range', 'user,date,user+date', 'date', '8', '', 69348, 'Using where; Using temporary; Using filesort'
1, 'SIMPLE', 'users', 'eq_ref', 'PRIMARY', 'PRIMARY', '8', 'scores.user_id', 1, 'Using where'

With limit: it's the same but the rows search are now 1806794 and it takes forever.

It's a partitioned InnoDB if that makes any difference, with all data being on one partition.

Best Answer

Things to try:

Adding an index on (user_id, date, score)

Group by only on scores table and then join to users:

SELECT s.total, u.name, u.gender, u.dob, u.country
FROM users AS u
  JOIN 
  ( SELECT user_id, SUM(score) AS total
    FROM scores
    WHERE date >= '2012-01-01' AND date < '2012-02-01'
    GROUP BY user_id
    HAVING SUM(score) >= 1000
    ORDER BY total DESC LIMIT 50
  ) AS s
      ON u.id = s.user_id
ORDER BY total DESC ;
Related Question