MySQL Forums
Forum List  »  Optimizer & Parser

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..

Options: ReplyQuote


Subject
Views
Written By
Posted
Simple query spending too much time in 'statistics' state without reason
11868
August 21, 2012 06:19AM


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.