Add an index to MySQL, but only if it doesn't exist

Question

Is there a way that we can run an ADD INDEX only to databases where it doesn't exist?

Sort of doing an ADD INDEX based on the results of SHOW INDEX ON..

Really trying to avoid the overhead of accidentally adding additional indexes where they already exist.

Answer 1

I guess you are looking for something like:

CREATE INDEX IF NOT EXISTS index_name ON table(column)

Sadly, this functionality does not exist in mySql. You can use stored procedure (as mentioned by Gordon Linoff) for checking if index exists and add index if not.

The information about indexes is available in INFORMATION_SCHEMA

You can do the following query to check for existing indexes:

SELECT COUNT(1) indexExists FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema=DATABASE() AND table_name='mytable' AND index_name='index_name';

If index already exists it gives 1 else 0. You can use this in a procedure for achieving what you want.

Also check this for more info.