Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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 ;
share|improve this answer
    
Thanks: yeah, for some reason I initially shyed away from using derived tables, but with a bit of tinkering, going down this route this has helped get better performance. –  Apemantus May 10 '13 at 1:02
add comment

Use correct ANSI group by (not the MySQL abomination extension) and see what happens

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 name,gender,dob,country
having sum(score)>=1000
order by sum(score) desc limit 50

Why?

GROUP BY in MySQL implies ORDER BY which causes the filesort here
Changing the query to be more standard may help the optimiser

More on the MySQL abominations extensions:

And from the MySQL Docs on ORDER BY Optimization

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

...

You have different ORDER BY and GROUP BY expressions.

share|improve this answer
    
Cool: that got the explain rows down to the same as the first query. I'm marking the other answer as "best", but this has been really useful: coming from SQL Server, I new MySQL handled group by more laxly, but I didn't realise it had a potential effect of queries. –  Apemantus May 10 '13 at 1:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.