1

We are in the process of moving from AWS RDS MySQL 5 to AWS RDS MySQL 8. We have a script that runs a few dozen queries. The last few look like this:

ALTER TABLE my_right_table ADD INDEX (col_a, col_b), ADD INDEX (col_c);
UPDATE my_left_table SET my_var=1 WHERE concat(col_a, col_b) in (SELECT col_c FROM my_right_table);

This works fine in MySQL 5 as is, and the 2nd query runs in seconds.

In MySQL 8 the 2nd query takes 20 minutes.

After much fiddling I have determined that if I throw a sleep in between the two queries, the 2nd query again runs in just seconds.

ALTER TABLE my_right_table ADD INDEX (col_a, col_b), ADD INDEX (col_c);
SELECT SLEEP(30);
UPDATE my_left_table SET my_var=1 WHERE concat(col_a, col_b) in (SELECT col_c FROM my_right_table);

Is there something about MySQL 8 that is different regarding index creation? Is it asynchronous or something?

Is the fact that it works on MySQL 5 just a fluke?

Should the index be available immediately after creating it?

4
  • 1
    Have you looked at the multi-table UPDATE syntax rather than a subquery?
    – danblack
    Nov 30 '21 at 5:09
  • 1
    How big is the table? What is the time from start of the Alter until the end of the Update?
    – Rick James
    Nov 30 '21 at 6:33
  • @RickJames ~3,000 rows, not very big. The ALTER statement takes less than a second, which is why I don't understand why the query doesn't use the index immediately after.
    – Nick P
    Nov 30 '21 at 14:52
  • Build a test case without the CONCAT and file a bug report at bugs.mysql.com
    – Rick James
    Nov 30 '21 at 17:15

Your Answer

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

Browse other questions tagged or ask your own question.