How can I employ "if exists" for creating or dropping an index in MySQL?

Question

I was wondering if there's a way to check if an index exists before creating it or destroying it on MySQL. It appears that there was a feature request for this a few years back, but I can't find any documentation for a solution. This needs to be done in a PHP app using MDB2.

Answer 1

It's not built in

However,

http://old.nabble.com/how-to-%22drop-index-if-exists%22-td14024229.html

explains how to write a stored procedure to implement DROP INDEX IF EXISTS, and it looks pretty easy to modify this code to work to implement CREATE INDEX IF EXISTS

Answer 2

IF EXISTS modifier is not built for DROP INDEX or CREATE INDEX yet. But you can check manually for the existence before creating/dropping an index.

Use this sentence to check whether the index already exists.

SHOW INDEX FROM table_name WHERE KEY_NAME = 'index_name'

• If the query returns zero (0) then the index does not exists, then you can create it.
• If the query returns a positive number, then the index exists, then you can drop it.