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