Bug #33409 InnoDb: No way to query index cardinality statistics without causing calculation
Submitted: 20 Dec 2007 16:05 Modified: 2 Feb 2011 21:12
Reporter: Keith Miller Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.0.45 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[20 Dec 2007 16:05] Keith Miller
Description:
Not sure if this is just an InnoDb issue.

both SHOW INDEXES and SELECT * FROM INFORMATION_SCHEMA.STATISTICS cause cardinality statistics to be recalculated.  When evaluating the reasons behind poor execution plans of queries 

How to repeat:
see tobe attached script 

1) create test table in the test schema;
2) populate test table with a reasonable set of data
3) run "SHOW INDEXES from test" and/or "SELECT * FROM information_schema.statistics where table_schema = 'test' and table_name = 'test'" multiple times

see varying cardinality values.

Suggested fix:
add an option to "SHOW INDEXES":
   "SHOW INDEXES from test NO-CALCULATE"

I'm not sure what the best suggestion for "select * from information_schema.statistics"

or add a configuration parameter to my.cnf like:
innodb_calc_index_stats_with_show: 
   defaulting to true for backwards compatibility? when false, do not manipulate statistics values
[20 Dec 2007 16:07] Keith Miller
console output of process to reproduce this bug

Attachment: mysqlBug_33409.txt (text/plain), 6.84 KiB.

[3 Jan 2008 16:39] Susanne Ebrecht
Many thanks for writing a feature request.
[2 Feb 2011 21:12] James Day
This won't be changed in 5.0. For 5.1 and later we introduced the setting innodb_stats_on_metadata that disables cardinality updating for many of the cases where it was done. That is covered by the feature request in bug #26598.

Because it's a feature that we added I'm closing this as a duplicate of the bug that added the feature. For 5.0 only it would be a wontfix closure - not going to backport the feature.

I got to this bug because even with innodb_stats_on_metadata off in 5.1 we have a case where selecting partition information from I_S seems to be causing stats to be updated. That will be filed as a new bug if necessary.