Simple query spending too much time in 'statistics' state without reason

[Vincent B.]

Hello,

I have a simple query on a single table which contains millions of rows.

SELECT a, c
FROM mytable
WHERE a IN (list of ids)
AND b IN (list of others ids)

The table 'mytable' has a primary index on (a, b). Both a and b are integer.
I use InnoDB.

CREATE TABLE `mytable` (
`a` INT(11) NOT NULL DEFAULT '0',
`b` INT(11) NOT NULL DEFAULT '0',
`c` INT(11) NOT NULL DEFAULT '0'
PRIMARY KEY (`a`,`b`,`c`),
KEY `speed2` (`a`,`c`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

SHOW TABLE STATUS LIKE '%mytable%';
NAME ENGINE VERSION ROW_FORMAT ROWS AVG_ROW_LENGTH Data_length Max_data_length Index_length Data_free AUTO_INCREMENT Create_time Update_time Check_time COLLATION CHECKSUM Create_options COMMENT
mytable INNODB 10 COMPACT 267184317 82 21978152960 0 8147435520 7340032 2012-08-16 15:04:48 latin1_swedish_ci

The query takes quite a long time so I wanted to investigate and I found out with SHOW PROFILE that most of the time (98%) is spent in the state 'statistics'.

The 'statistics' state is used by MySQL to calculate statistics to develop a query execution plan (which indexes to use and which order to join tables). There is no JOIN in this query and I tried to 'USE INDEX(PRIMARY)' or 'FORCE INDEX(PRIMARY)' but it doesn't change anything.

So it does not make sense at all to me. Any idea why time is spent in 'statistics' state and how I could run my query faster?

Thanks

Vincent



Edited 1 time(s). Last edit at 08/21/2012 07:22AM by Vincent B..