In mysql 5.02 and higher, you can use the (rather cryptically named) STATISTICS table in the information_schema (see: http://dev.mysql.com/doc/refman/5.0/en/statistics-table.html
This contains a row for each index *column* in the tables that are accessible to the current user. There is NO separate system view in the information schema that lists each index separately.
The following query retrieves a row for each individual index:
, if(non_unique,'NON UNIQUE','UNIQUE') as uniqueness
, group_concat(column_name order by seq_in_index) as columns
group by table_schema
to see the indexes for the current schema, add a where clause to this saying:
WHERE table_schema = SCHEMA()
To check if a index exists, just use a query like:
[context, f.e. a IF statement inside a procedure] exists (
where table_schema = 'myschema'
and table_name = 'mytable'
and index_name = 'myindex'
(You really need to use the table name to identify an index in mysql. In mysql, indexes have unique names within the table for which they are defined. So, there can be different indexes with identical names within on schema ,provided they are defined for diferent tables )
Edited 1 time(s). Last edit at 12/24/2005 02:39PM by Roland Bouman.