Newsletters older than 6 months may have links that are out of date.
Please use the Search to check for updated links.
Peter Zaitsev
What is the method to analyze InnoDB tables? How can we collect statistics, so that the optimizer can select the best execution plan?
With InnoDB, table statistics are updated automatically, so you do not need to run analyze table. They could be a bit less accurate compared to freshly analyzed MyISAM table as they are estimated by doing random index dives instead of table scan for calculation, as it is done with MYISAM tables. On other hand, you usually do not need 100% accurate statistics for optimizer to select proper execution plan.
Also note MySQL uses index cardinality data only for non-constant join optimization, in other cases it estimates exact stats for passed constant, so queries of this type select * from A where a=<constant1 and b=<constant2 might use different indexes based on constant values.
If you have particular queries which are executing slower than you think they should, you should study the EXPLAIN output for them with "SHOW CREATE TABLE" for the involved tables. Or you could purchase MySQL support contract and let the MySQL developers help you on the optimizing task.
© 2010, Oracle Corporation and/or its affiliates