slow query sorting on group maximum
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;