Description:
When we changed the column type of a table containing a large volume of data using parallel ALTER TABLE statements, queries against the modified tables suddenly became slower.
Upon examining the "EXPLAIN" output of the slow queries, we found that the join order between multiple tables and the indexes used were different from the usual execution plans.
After running OPTIMIZE TABLE on some of the modified tables, the execution plans reverted to their original state and performance improved.
Even for the tables where OPTIMIZE TABLE was not executed, the performance of the slow queries eventually returned to normal after some time. Checking the execution plans again, they also had reverted to their usual state.
At the point when the ALTER TABLE operation to change the column type is completed, are the index statistics already updated to reflect the new column type?
As an example, we performed a column type change on a table like the one below:
```sql
CREATE TABLE example_NNN (
pk1 VARCHAR(64) NOT NULL,
pk2 VARCHAR(64) NOT NULL,
pk3 VARCHAR(64) NOT NULL,
val1 VARCHAR(64) NOT NULL,
val2 VARCHAR(64) NOT NULL,
val3 VARCHAR(64) NOT NULL,
PRIMARY KEY (pk1,pk2,pk3),
INDEX (val1,pk3),
INDEX (val2,pk3),
INDEX (val3,pk3) );
ALTER TABLE example_NNN
MODIFY COLUMN pk3 enum('ONE', 'TWO', 'THREE');
```
How to repeat:
none
Description: When we changed the column type of a table containing a large volume of data using parallel ALTER TABLE statements, queries against the modified tables suddenly became slower. Upon examining the "EXPLAIN" output of the slow queries, we found that the join order between multiple tables and the indexes used were different from the usual execution plans. After running OPTIMIZE TABLE on some of the modified tables, the execution plans reverted to their original state and performance improved. Even for the tables where OPTIMIZE TABLE was not executed, the performance of the slow queries eventually returned to normal after some time. Checking the execution plans again, they also had reverted to their usual state. At the point when the ALTER TABLE operation to change the column type is completed, are the index statistics already updated to reflect the new column type? As an example, we performed a column type change on a table like the one below: ```sql CREATE TABLE example_NNN ( pk1 VARCHAR(64) NOT NULL, pk2 VARCHAR(64) NOT NULL, pk3 VARCHAR(64) NOT NULL, val1 VARCHAR(64) NOT NULL, val2 VARCHAR(64) NOT NULL, val3 VARCHAR(64) NOT NULL, PRIMARY KEY (pk1,pk2,pk3), INDEX (val1,pk3), INDEX (val2,pk3), INDEX (val3,pk3) ); ALTER TABLE example_NNN MODIFY COLUMN pk3 enum('ONE', 'TWO', 'THREE'); ``` How to repeat: none