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

Here is my 4 liner:

set @exist := (select count(*) from information_schema.statistics where table_name = 'table' and index_name = 'index' and table_schema = database());
set @sqlstmt := if( @exist > 0, 'select ''INFO: Index already exists.''', 'create index i_index on tablename ( columnname )');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;

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.