Simple query spending too much time in 'statistics' state without reason
Posted by:
Vincent B.
Date: August 21, 2012 06:19AM
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..
Subject
Views
Written By
Posted
Simple query spending too much time in 'statistics' state without reason
12376
August 21, 2012 06:19AM
6158
August 22, 2012 07:44PM
6595
August 24, 2012 06:24AM
4877
August 28, 2012 01:53AM
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.