ANALYZE TABLE not working correctly
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