Way to check for an index before adding?

sneakyimp

I'd like to add an index to a table, but ONLY IF there is not already an index for that particular column:

ALTER TABLE myTable ADD INDEX ( `countries_iso_code_2` );

Is there some way to check if an index for column 'countries_iso_code_2' already exists before I go ahead and add one?

dagon

describe myTable
or
SHOW INDEX FROM myTable

sneakyimp

I'm familiar with describe/explain/show. Let me be more clear.

I want to write some sql which will check to see if there is an index for a table on a particular column, 'countries_iso_code_2', and if no such index exists, it alters the table to add such an index.

Unfortunately, this produces an error:

SHOW INDEX FROM countries WHERE Column_name='countries_iso_code_2';

dagon

odd, works for me:

SHOW INDEX FROM notifications_sent_to WHERE Column_name = 'id'

dagon

Extensions to SHOW Statements

"These extensions are available beginning with MySQL 5.0.3. "

you many be running an older version than me.

sneakyimp

Yes I am, it's version 4. sigh.

dagon

sneakyimp;10927646 wrote:
Yes I am, it's version 4. sigh.

It's upgrade time:p

sneakyimp

I'll tell the client 🙁