MySQL Forums :: InnoDB :: ANALYZE TABLE not working correctly

Advanced Search

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:

company VARCHAR(8),
driver INT,
vehicle VARCHAR(16),
activity ENUM,
KEY (company, vehicle, activity, time),
KEY (company, driver, activity, time)

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!



Options: ReplyQuote

Subject Views Written By Posted
ANALYZE TABLE not working correctly 3800 Ben Clewett 10/03/2008 04:36AM
Re: ANALYZE TABLE not working correctly 1692 Ben Clewett 10/07/2008 01:45AM
Re: ANALYZE TABLE not working correctly 1720 KimSeong Loh 10/07/2008 05:57PM
Re: ANALYZE TABLE not working correctly 1607 Ben Clewett 10/08/2008 03:35AM

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.