MySQL Forums
Forum List  »  InnoDB

ANALYZE TABLE not working correctly
Posted by: Ben Clewett
Date: October 03, 2008 04:36AM

Using 5.0.41 (which I know is old, but I believe still relevant)

I am experiencing a scenario where after a server restart OR after running ANALYSE TABLE, the metrics are not correct gathered and queries start using the wrong keys.

This is bad, as the table had over 100 million rows and the wrong key causes a full table scan, taking the query time from sub-second to over five minutes.

This only happens about 1 in 20 times, but is serious.

[The confusion is between two composite keys:

CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
company VARCHAR(8),
driver INT,
vehicle VARCHAR(16),
activity ENUM,
time TIMESTAMP,
KEY (company, vehicle, activity, time),
KEY (company, driver, activity, time)
) ENGINE=InnoDB;

If the wrong one is used, I get a full-table-scan.]

This can be caused AND fixed by running ANALYZE TABLE, the result is random.

---------

My guess is that ANALYSE is not checking enough data in order to calculate the metrics used by the query optimizer. This is confirmed by a EXPLAIN which drastically under estimates the row count, by a factor of over 100,000.

My guess therefore is making ANALYZE check a greater amount of data will therefore fix the problem.

So my question is, how do I make the ANALYZE check more data?

This is a serious problem for us, any serious answers are extremely welcome!

Regards,

Ben

Options: ReplyQuote


Subject
Views
Written By
Posted
ANALYZE TABLE not working correctly
3844
October 03, 2008 04:36AM


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.