drop and re add an index on innodb table causes slow performance

Question

We just made a code and db update, that involves a table on production. This INNODB table was "altered" by adding a new index and dropped the old one. The new is made by 3 params and the old was 2 params. This slow down all the performace dramatically.

The table is an "innodb" on mysql 5.7.33.

is the slowness maybe caused that the new index? is safe to re insert the old index, and it can be a solution? Any tips wellcome

thanks

Answer 1

A guess...

Now that DROP INDEX is "instant" and ADD INDEX is done in the background, here is a possible explanation:

ALTER TABLE t
    DROP INDEX a_b,
    ADD INDEX "a_b_c" (a,b,c);

may run this way:

1. Instantly DROP the old index -- now the table has no index starting with (a,b)
2. Launch a background task to do the ADD.
3. Return to the user -- allowing them to perform queries for which there may be no index
4. Eventually the new index is finished.
5. Now the same query will be fast.

If that guess is correct, then this would be the cure:

1. ALTER TABLE ADD INDEX "a_b_c" (a,b,c);
2. Wait a while. (No easy way to tell when it will be finished -- Watching SHOW PROCESSLIST is a crude solution.).
3. ALTER TABLE DROP INDEX "a_b";