MySQL Forums
Forum List  »  Optimizer & Parser

Re: slow query sorting on group maximum
Posted by: Dong Hoon Van Uytsel
Date: November 19, 2010 04:30PM

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



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
1432
November 19, 2010 04:30PM
1905
November 19, 2010 07:05PM
1293
November 19, 2010 07:20PM
1293
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.