Re: slow query sorting on group maximum

[Dong Hoon Van Uytsel]

Hi Rick

Thanks for your valuable suggestions. I will be trying the combined index(expiry, INTID) into data.
As to the 'event' table, it is an unfortunate typo in the post only; I should have written "intervention".
Below is the requested information; hopefully it sheds some more light on the issue.

-- Table definitions after suggested changes (irrelevant columns/keys omitted):


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 NOT NULL default '1970-01-01 00:00:00',
  `PATIENTID` binary(16) NOT 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) NOT NULL,
  `expiry` datetime NOT NULL default '2200-01-01 00:00:00',
  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


-- Table sizes


mysql> show table status like 'intervention'\G 
*************************** 1. row ***************************
           Name: intervention
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 254251
 Avg_row_length: 142
    Data_length: 36241408
Max_data_length: 0
   Index_length: 57802752
      Data_free: 0
 Auto_increment: 302172
    Create_time: 2010-11-07 14:09:51
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 172032 kB; (`LOCID`) REFER `test2/location`(`LOCID`)

mysql> show table status like 'identity'\G
*************************** 1. row ***************************
           Name: identity
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 119222
 Avg_row_length: 101
    Data_length: 12075008
Max_data_length: 0
   Index_length: 17891328
      Data_free: 0
 Auto_increment: 119386
    Create_time: 2010-11-07 14:08:43
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 172032 kB; (`INDID`) REFER `test2/individual`(`INDID`); (`ROLEID`) R

mysql> show table status like 'data'\G    
*************************** 1. row ***************************
           Name: data
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 252142
 Avg_row_length: 314
    Data_length: 79265792
Max_data_length: 0
   Index_length: 86818816
      Data_free: 0
 Auto_increment: 409302
    Create_time: 2010-11-07 14:08:12
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 172032 kB; (`INTID`) REFER `test2/intervention`(`INTID`); (`lifestag

-- Explain

mysql> explain 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
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: data
         type: range
possible_keys: INTID,ix_data_expiry
          key: ix_data_expiry
      key_len: 8
          ref: NULL
         rows: 126072
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: intervention
         type: eq_ref
possible_keys: ix_intervention_INTID,ix_intervention_PATIENTID
          key: ix_intervention_INTID
      key_len: 16
          ref: test2.data.INTID
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: identity
         type: eq_ref
possible_keys: ix_identity_IDENTID
          key: ix_identity_IDENTID
      key_len: 16
          ref: test2.intervention.PATIENTID
         rows: 1
        Extra: Using index


-- Buffer sizes

mysql> show variables like '%buffer%'\G
*************************** 1. row ***************************
Variable_name: bulk_insert_buffer_size
        Value: 8388608
*************************** 2. row ***************************
Variable_name: innodb_buffer_pool_awe_mem_mb
        Value: 0
*************************** 3. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 1258291200
*************************** 4. row ***************************
Variable_name: innodb_log_buffer_size
        Value: 1048576
*************************** 5. row ***************************
Variable_name: join_buffer_size
        Value: 131072
*************************** 6. row ***************************
Variable_name: key_buffer_size
        Value: 16777216
*************************** 7. row ***************************
Variable_name: myisam_sort_buffer_size
        Value: 8388608
*************************** 8. row ***************************
Variable_name: net_buffer_length
        Value: 16384
*************************** 9. row ***************************
Variable_name: preload_buffer_size
        Value: 32768
*************************** 10. row ***************************
Variable_name: read_buffer_size
        Value: 131072
*************************** 11. row ***************************
Variable_name: read_rnd_buffer_size
        Value: 262144
*************************** 12. row ***************************
Variable_name: sort_buffer_size
        Value: 2097144