Not the answer you need?
Register and ask your own question!

Slow ALTER query when the database engine is InnoDb/xTradb?!

clouseauclouseau ContributorCurrent User Role Contributor
I have a table that is in InnoDB engine, quite a simple one with 25000 rows. When I do a simple ALTER, it runs for almost 10 minutes:

mysql> ALTER TABLE `quote_followups_istvan` ADD `customer_ip2` VARCHAR(20) NOT NULL DEFAULT '' AFTER `comment`;
Query OK, 0 rows affected (10 min 52.82 sec)
Records: 0 Duplicates: 0 Warnings: 0

But when I change it's engine to MyISAM, I get this:

mysql> alter table quote_followups_istvan engine="MyISAM";
Query OK, 25053 rows affected (0.56 sec)
Records: 25053 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE `quote_followups_istvan` ADD `customer_ip3` VARCHAR(20) NOT NULL DEFAULT '' AFTER `comment`;
Query OK, 25053 rows affected (0.37 sec)
Records: 25053 Duplicates: 0 Warnings: 0

So 10minutes vs 0.37s....

What amd I missing here?

Edit:
In fact that speciffic ALTER query took a while (obviously), and I'm not sure why. For example, altering the engine was ok, so it's not the ALTER itself that is the issue

Comments

  • clouseauclouseau Contributor Current User Role Contributor
    To make is clear, the idea is not at all to convert the table to MyISAM, do the altering and then convert it back to InnoDB. Converting it to MyISAM was just an experiment to see how long the query would take then. The real questin is, how to make that fast(er) with InnoDB.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.