Bug #15444 Non-unique indexes hidden on Information_schema
Submitted: 2 Dec 2005 18:56 Modified: 2 Dec 2005 20:27
Reporter: Eber Duarte Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:5.0.16 OS:Linux (Debian Linux i386)
Assigned to: CPU Architecture:Any

[2 Dec 2005 18:56] Eber Duarte
Description:
It's not possible to see in key_column_usage table, information about non unique indexes. For exemple:

mysql>CREATE TABLE foo (
    -> col1 int primary key,
    -> name char(30) not null,
    -> index idx (name)
    ->);

mysql> select * from key_column_usage where table_name like 'foo' and table_schema='test'\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: NULL
            CONSTRAINT_SCHEMA: test
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: NULL
                 TABLE_SCHEMA: test
                   TABLE_NAME: foo
                  COLUMN_NAME: col1
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
1 row in set (0.00 sec)

There is no information about idx index. It would be usefull if I could see this index in information_schema tables?

Regards.

How to repeat:
It's not a bug!
[2 Dec 2005 20:27] Valeriy Kravchuk
Thank you for a feature request. MySQL way to get this kind of information was to use SHOW INDEX FROM statement, but separate INDEX and/or INDEXED_COLUMNS tables for the INFORMATION_SCHEMA will be useful too.