Of course we know that in InnoDB, non-PK indexes also contain the value of the primary key as the last field of the index, except this is internal and hidden from the visible table structure.
So the output is, while correct in terms of what's stored, confusing.
To "unconfuse" it, the INNODB_INDEX_STATS output could just colcount-1 for any non-PK index.
Pretty simple, but I have to say I'm ambivalent about whether it's worthwhile - it's good to have this documented explicitly of course, to at least have a reference point in case of confusion 
|
|
table_schema table_name index_name fields rows_per_key index_total_pages index_leaf_pages
|
weather obs_daily PRIMARY 1 1 206976 180899
|
weather obs_daily idx_obs_daily_1 3 2884335, 3533, 1 67328 58696
|
weather obs_daily idx_obs_daily_2 2 8472, 1 28032 24442
|
Other database servers like above showed 3 for the same schema
I cannot repeat this, I'm getting the same result as on your "other database servers".
Can you create a repeatable test case for this?
I cannot either, although I can get 3 fields for idx_obs_daily_2 if I try hard enough:
MariaDB [test]> CREATE TABLE `obs_daily` ( `sLocType` varchar(10) NOT NULL DEFAULT '', `sLocCode` varchar(30) NOT NULL DEFAULT '', `dtDate` date NOT NULL DEFAULT '0000-00-00', UNIQUE KEY `idx_obs_daily_1` (`sLocType`,`sLocCode`,`dtDate`), KEY `idx_obs_daily_2` (`dtDate`) ) ENGINE=InnoDB AUTO_INCREMENT=23562482 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; |
Query OK, 0 rows affected (0.99 sec) |
|
|
MariaDB [test]> select * from information_schema.innodb_index_stats; |
+--------------+------------+-----------------+--------+--------------+-------------------+------------------+ |
| table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages |
|
+--------------+------------+-----------------+--------+--------------+-------------------+------------------+ |
| test | obs_daily | idx_obs_daily_1 | 3 | 0, 0, 0 | 1 | 1 |
|
| test | obs_daily | idx_obs_daily_2 | 3 | 0, 0, 0 | 1 | 1 |
|
+--------------+------------+-----------------+--------+--------------+-------------------+------------------+ |
In any case, this is documented explicitly in Percona docs:
http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_stats.html
fields – How many fields the index key has. (it is internal structure of InnoDB, it may be larger than the CREATE TABLE).
Given that it's "design", explicitly documented by Percona, and that the table doesn't even exist in XtraDB 5.6 / MariaDB 10.0, I suppose the reasonable course of action is not to do anything about it. Does everyone agree?
fair call.
Is somebody disagrees, please comment to re-open.