Currently I have an update-logic written in PHP which can update multiple tables inside a MySQL-database. Whenever a certain PHP-script is getting executed, the update-logic checks if the database is up-to-date (based on a version-flag which is saved somewhere else) and applies all necessary updates if not.
One part of the update-logic should add an index for a specific table-column:
if ($database_version < 1337) {
if (!get_results("SHOW INDEX FROM my_table WHERE Key_name = 'my_key';")) {
query("ALTER TABLE my_table ADD INDEX(my_key);");
}
set_database_version(1337);
}
As you can see I do the following setps:
Now imagine that the update-logic is getting triggered by an user. Because the table is very big, the creation of the index can take a while. Before the creation of the index is done, another user triggers the update-logic. Because the index is not finished yet, the initial check returns FALSE and the update-logic starts another creation-query for this index - which means the index is getting built/created twice or even more often.
First I assumed that during the creation of an index a specific entry is made into a table before it is getting filled with values. But this seems to be not the case because the query
SHOW INDEX FROM my_table WHERE Key_name = 'my_key';
returns FALSE even when this index is just getting created.
My question: Is there a way to check via MySQL/PHP if an index exists OR is just getting created so I can prevent that it gets created multiple times?
There is a way to see the index creation progress in MySql as described here: https://www.percona.com/blog/2014/02/26/monitor-alter-table-progress-innodb_file_per_table/.
But I guess that would be too much to do when this can be achieved in a much simpler way.
My suggestion would be to keep a lock variable set whenever a user is going to create the index. When the other user tries to execute the script the lock variable is checked if it's set or not and only when it's not set, the script goes forward to create the index.
----- not yet released 5.7.0 Milestone 10 -- Functionality Added or Changed -- -----
The server now issues a warning if an index is created that duplicates an existing index, or an error in strict SQL mode. (Bug #37520, Bug #11748842)
So,...
Alternatively, rummage through information_schema.KEY_COLUMN_USAGE to discover if an equivalent index exists.