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.

Normally, adding an index is a manual operation, done once for a given table. Doing the check before adding the index does not seem unduly burdensome, although you could probably put together a stored procedure to implement this. — Gordon Linoff
– Gordon Linoff, Commented Apr 11, 2015 at 14:02
Thanks Gordon, it was to run against 400+ databases, most of which have it already but a few were created without. — Jamie Abbott
– Jamie Abbott, Commented Apr 11, 2015 at 22:38

Community · Accepted Answer · 2017-04-13 12:42:39Z

4

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.

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Apr 11, 2015 at 14:13

karthik manchala

13.7k1 gold badge34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jamie Abbott Over a year ago

Thanks Karthik, I think I'll end up running it in PHP with two separate queries.

Alexey Burdin Over a year ago

You can look up on column_name instead of index_name, if needed.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related