MySQL Forums
Forum List  »  Optimizer & Parser

slow query sorting on group maximum
Posted by: Dong Hoon Van Uytsel
Date: November 05, 2010 09:11AM

Hello, I am struggling with getting the below query responsive enough to be used to browse through a list of patients together with their most recent intervention (linked with "identity") using pages of 30 patients at a time. The list needs to be sorted with most recent "interventions" listed first, where only interventions with non-expired data need to be considered.

The query now takes 1.5 seconds, which makes for a sluggish user experience. Perhaps anyone here sees a possibility to obtain the same result in a more efficient way?

I also included the "explain" output, which lists "filesort" - perhaps this explains the slow execution.

The data definitions are mentioned below.

QUERY:

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;

EXPLAIN OUTPUT:

+----+-------------+--------------+--------+-------------------------------------------------+-----------------------+---------+----------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+-------------------------------------------------+-----------------------+---------+----------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | data | range | INTID,ix_data_expiry | ix_data_expiry | 9 | NULL | 178323 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | intervention | eq_ref | ix_intervention_INTID,ix_intervention_PATIENTID | ix_intervention_INTID | 16 | tst.data.INTID | 1 | |
| 1 | SIMPLE | identity | eq_ref | ix_identity_IDENTID | ix_identity_IDENTID | 16 | tst.intervention.PATIENTID | 1 | Using index |
+----+-------------+--------------+--------+-------------------------------------------------+-----------------------+---------+----------------------------+--------+----------------------------------------------+

DATA DEFINITION (irrelevant columns left out):

CREATE TABLE `identity` (
`id` int(11) NOT NULL auto_increment,
`IDENTID` binary(16) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ix_identity_IDENTID` (`IDENTID`),
) ENGINE=InnoDB AUTO_INCREMENT=119386 DEFAULT CHARSET=utf8;

CREATE TABLE `intervention` (
`id` int(11) NOT NULL auto_increment,
`INTID` binary(16) NOT NULL,
`starttime` datetime default NULL,
`PATIENTID` binary(16) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ix_intervention_INTID` (`INTID`),
KEY `ix_intervention_starttime` (`starttime`),
KEY `ix_intervention_PATIENTID` (`PATIENTID`),
) ENGINE=InnoDB AUTO_INCREMENT=302172 DEFAULT CHARSET=utf8;

CREATE TABLE `data` (
`id` int(11) NOT NULL auto_increment,
`INTID` binary(16) default NULL,
`expiry` datetime default NULL,
PRIMARY KEY (`id`),
KEY `INTID` (`INTID`),
KEY `ix_data_expiry` (`expiry`),
CONSTRAINT `data_ibfk_1` FOREIGN KEY (`INTID`) REFERENCES `intervention` (`INTID`),
) ENGINE=InnoDB AUTO_INCREMENT=409302 DEFAULT CHARSET=utf8;

Options: ReplyQuote


Subject
Views
Written By
Posted
slow query sorting on group maximum
3398
November 05, 2010 09:11AM
1359
November 06, 2010 10:56AM
1369
November 07, 2010 01:05PM
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.