MySQL Forums
Forum List  »  Optimizer & Parser

Re: slow query sorting on group maximum
Posted by: Rick James
Date: November 06, 2010 10:56AM

select  distinct identity.id, max(intervention.starttime) as max_1
    from  identity
    join  event 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;
DISTINCT and GROUP BY -- usually redundant.
LIMIT and OFFSET; smells like pagination?
'OR' virtually eliminates any chance of using an INDEX; can you get rid of NULLs in `expiry`?
Other fields, such as PATIENTID can be NULL; is that reasonable?
For intervention, why have an AUTO_INCREMENT, when you seem to have a perfectly usable UNIQUE key of INTID?
Is this query a subquery in something else?
Are any pairs of tables 1:1?
Is innodb_buffer_pool_size set to 70% of _available_ RAM?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: slow query sorting on group maximum
1353
November 06, 2010 10:56AM
1357
November 07, 2010 01:05PM
1898
November 19, 2010 07:05PM
1282
November 19, 2010 07:20PM
1282
November 19, 2010 07:14PM


Sorry, you can't reply to this topic. It has been closed.

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.