MySQL Forums
Forum List  »  Optimizer & Parser

Re: slow query sorting on group maximum
Posted by: Rick James
Date: November 19, 2010 07:05PM

Currently:
select  identity.id,
        max(intervention.starttime) as max_1
    from  identity
    join  intervention on identity.IDENTID=intervention.PATIENTID
    join  data on data.INTID=intervention.INTID
    where  data.expiry is null
       or  data.expiry >= "2010-11-05 15:55"
    group by  identity.id
    order by  max_1 desc
    limit  91,30\G

That's nasty. It filters on one table, grabs the starttime from the next table, then grabs the GROUP BY value from the third table. At this point, it has a temp table of thousands (millions?) of rows. Now it can start doing the GROUP BY. When that is finished, it still needs to sort to achieve the ORDER BY. Then it skips over 91 rows before delivering 30.

If the relevant columns were all in one table, and you had a good index on it, then (and only then?) could this query run significantly faster.

Options: ReplyQuote


Subject
Views
Written By
Posted
1359
November 06, 2010 10:56AM
1369
November 07, 2010 01:05PM
Re: slow query sorting on group maximum
1905
November 19, 2010 07:05PM
1293
November 19, 2010 07:20PM
1292
November 19, 2010 07:14PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.