MySQL row count incorrect when reading a table's indexes from INFORMATION_SCHEMA.STATISTICS

Question

I'm seeing some strange results when querying INFORMATION_SCHEMA.STATISTICS for index names. When I do "SELECT * ...", I see 2 indexes, but "SELECT COUNT(*) ..." it says count(*) is 0. Anyone know why? Is there a better way to get indexes on a table, ideally not MySQL-specific?

Example:

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
table_schema = 'mifostest' AND  table_name ='CUSTOMER' AND INDEX_NAME =
'CUSTOMER_BRANCH_SEARCH_IDX';
+----------+
| COUNT(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.00 sec)

However...

mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema =
'mifostest' AND  table_name ='CUSTOMER' AND INDEX_NAME =
'CUSTOMER_BRANCH_SEARCH_IDX'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
 TABLE_SCHEMA: mifostest
   TABLE_NAME: CUSTOMER
   NON_UNIQUE: 1
 INDEX_SCHEMA: mifostest
   INDEX_NAME: CUSTOMER_BRANCH_SEARCH_IDX
 SEQ_IN_INDEX: 1
  COLUMN_NAME: BRANCH_ID
    COLLATION: A
  CARDINALITY: 0
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: BTREE
      COMMENT: 
*************************** 2. row ***************************
TABLE_CATALOG: NULL
 TABLE_SCHEMA: mifostest
   TABLE_NAME: CUSTOMER
   NON_UNIQUE: 1
 INDEX_SCHEMA: mifostest
   INDEX_NAME: CUSTOMER_BRANCH_SEARCH_IDX
 SEQ_IN_INDEX: 2
  COLUMN_NAME: SEARCH_ID
    COLLATION: A
  CARDINALITY: 0
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: BTREE
      COMMENT: 
2 rows in set (0.00 sec)

More information:

• I'm using MySQL 5.1.31-1ubuntu2 on Ubuntu 9.04.
• "SHOW CREATE TABLE" does show the indexes.

Thanks! -Adam

Answer 1

The ANSI/ISO SQL standard doesn't have anything to say about indexes, so I doubt there is a "not MySQL specific" way to query for them. The standard's INFORMATION_SCHEMA includes CONSTRAINTS, ASSERTIONS, and KEY_COLUMN_USAGE among the many base tables, but there's nothing about indexes. Even STATISTICS is not mentioned in the standard that I can find.

What you report sounds like a bug to me. If SELECT * returns rows, SELECT COUNT(*) shouldn't return zero. Perhaps there has been a regression of this bug.