drop and re add an index on innodb table causes slow performance
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
DROP INDEXis "instant" and
ADD INDEXis 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:
DROPthe old index -- now the table has no index starting with
- Launch a background task to do the
- Return to the user -- allowing them to perform queries for which there may be no index
- Eventually the new index is finished.
- Now the same query will be fast.
If that guess is correct, then this would be the cure:
ALTER TABLE ADD INDEX "a_b_c" (a,b,c);
- Wait a while. (No easy way to tell when it will be finished -- Watching
SHOW PROCESSLISTis a crude solution.).
ALTER TABLE DROP INDEX "a_b";