0

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

9
  • Did you analyze the table after the new index was added? Jan 24 at 14:50
  • @GerardH.Pille analyze the table put in a lock right? if yes its a problem right now
    – JahStation
    Jan 24 at 14:59
  • 1
    And creating the new index didn't need a lock? Jan 24 at 15:18
  • 1
    Percona says that since 5.7.20, analyze no longer locks: percona.com/blog/2018/03/27/…. Can you try it on a development database? Jan 24 at 15:25
  • i did and the result is "table ok"
    – JahStation
    Jan 24 at 16:09

1 Answer 1

0

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";
1
  • i still investigate, ill let you know if I solve somethings, thanks for your reply!
    – JahStation
    Jan 25 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.