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

**URL:** <https://forums.percona.com/t/slow-alter-query-when-the-database-engine-is-innodb-xtradb/5715>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [July 5, 2017, 4:06am UTC](https://forums.percona.com/t/slow-alter-query-when-the-database-engine-is-innodb-xtradb/5715 "2017-07-05T04:06:23Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![clouseau](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/clouseau/32/31_2.png) [@clouseau](https://forums.percona.com/u/clouseau)\
**Post date:** [July 5, 2017, 4:06am UTC](https://forums.percona.com/t/slow-alter-query-when-the-database-engine-is-innodb-xtradb/5715/1 "2017-07-05T04:06:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![clouseau](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/clouseau/32/31_2.png) [@clouseau](https://forums.percona.com/u/clouseau)\
**Post date:** [July 5, 2017, 7:09am UTC](https://forums.percona.com/t/slow-alter-query-when-the-database-engine-is-innodb-xtradb/5715/2 "2017-07-05T07:09:20Z")

</div>

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.