Select distinct index names

Question

I need to select all indexes from a given table.

But, it looks like MySQL creates multiple indexes with the same name with all the possible column combinations when there is more than one column in the index.

So SHOW INDEXES returns multiple times the same value.

Is there a way to do a select distinct to get the index names from a given table?

And if possible that is not MySQL specific.

Are you sure the names are the same? Which column of the `SHOW INDEXES` do yo take? It should be `Key_name` which must be unique. — Grzegorz, Oct 25 '12 at 00:55
Yes 100% sure. Key_name is not unique when I do a SHOW INDEXES. But I have different values for Column_name. — student310, Oct 25 '12 at 00:58

score 2 · Accepted Answer · answered Oct 25 '12 at 00:57

2

Please try this query:

SELECT DISTINCT INDEX_NAME FROM information_schema.statistics
  WHERE table_schema = 'your_schema'
    AND table_name = 'your_table'

answered Oct 25 '12 at 00:57

RGO

4,586
3
26
40

1

You're welcome! btw, `MySQL` doesn't create multiple indexes. It's just because `SHOW INDEXES` gives query result as one record per column per index. – RGO Oct 25 '12 at 01:04
1

Thank you for this answer @RGO. I've converted it into a full Java function for anyone who needs to know how to use it, and posted it here: http://stackoverflow.com/a/38670382/453673 – Nav Jul 30 '16 at 04:39
@Nav But be aware that this query works only for MySQL. – RGO Jul 30 '16 at 13:37

Select distinct index names

1 Answers1