How to Employ "If Exists" for Creating or Dropping an Index in MySQL

Why DROP INDEX IF EXISTS generates a MySQL note

This is what I have founded here: https://mariadb.com/kb/en/drop-index/

If the IF EXISTS clause is used, then MariaDB will return a warning
instead of an error if the index does not exist.

Hope this helps.

Determining if MySQL table index exists before creating

SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE
`TABLE_CATALOG` = 'def' AND `TABLE_SCHEMA` = DATABASE() AND
`TABLE_NAME` = theTable AND `INDEX_NAME` = theIndexName

What happens if I drop a MySQL column without dropping its index first?

According to the MySQL 5.1 Reference Manual:

If columns are dropped from a table,
the columns are also removed from any
index of which they are a part. If all
columns that make up an index are
dropped, the index is dropped as well.
If you use CHANGE or MODIFY to shorten
a column for which an index exists on
the column, and the resulting column
length is less than the index length,
MySQL shortens the index
automatically.

How to check if an index exists on a table field in MySQL

Use SHOW INDEX like so:

SHOW INDEX FROM [tablename]

Docs: https://dev.mysql.com/doc/refman/5.0/en/show-index.html

---

Related Topics