Skip navigation links

MySQL Forums :: Information Schema :: index checking?


Advanced Search

Re: index checking?
Posted by: Roland Bouman ()
Date: December 24, 2005 02:38PM

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:

select table_schema
, table_name
, if(non_unique,'NON UNIQUE','UNIQUE') as uniqueness
, index_name
, index_type
, group_concat(column_name order by seq_in_index) as columns
from information_schema.statistics
group by table_schema
, table_name
, non_unique
, index_name
, index_type

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 (
select null
from information_schema.statistics
where table_schema = 'myschema'
and table_name = 'mytable'
and index_name = 'myindex'
) [context]

(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.

Options: ReplyQuote


Subject Views Written By Posted
index checking? 5398 Les Stockton 12/23/2005 10:49AM
Re: index checking? 5753 Roland Bouman 12/24/2005 02:38PM
Re: index checking? 3110 Les Stockton 12/27/2005 09:18AM
Re: index checking? 2731 Roland Bouman 12/27/2005 01:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.