CODEWITHSUNDEEP
mysqlsqldatabase
Mellon
Mellon

Reputation: 38882

CREATE INDEX with condition?

I am using MySQL v5.1.

I would like to create index on a table by executing the following SQL statement:

CREATE INDEX index_name
ON table_name (column_name)

But, I wan to firstly check if the index on that column has already been created or not, if not, create it (otherwise do not create). What is the SQL syntax for this?

Upvotes: 1

Views: 3424

Answers (2)

Trott
Trott

Reputation: 70143

You want SHOW INDEX.

To get all the indexes on a table:

SHOW INDEX FROM table_name

MySQL allows you to add a WHERE clause to limit the results as well.

Lock the table while you're checking to see if the index exists (and if it doesn't exist, creating the index) so that another process doesn't create the index right after you've checked for it but before you've created it yourself.

Upvotes: 0

a1ex07
a1ex07

Reputation: 37382

Mysql doesn't have IF NOT EXISTS for CREATE INDEX. You can work it out by querying information_schema.statistics table. Take a look here, there is an example of stored procedure that does what you are looking for (search for "CREATE INDEX IF NOT EXISTS" on the page)

Upvotes: 1

Related Questions