ALTER Optimization

August 14, 2012

One of my colleague asked me : « Is an ALTER TABLE with many specifications is faster than 1 ALTER TABLE by specification ? »

The answers seems quite intuitive, anyway figures are better than a long speech…


Context :

  • MySQL Community Server 5.5.22
  • InnoDB table with 8 539 238 rows.
  • 17 columns with INT, CHAR, VARCHAR, TEXT, DATETIME, … fields



Adding 3 indexes (with a single command)



Total: 146.43 sec



Deleting the index (with a single command)



Note: With MySQL 5.5, delete secondary indexes (not PK) is costless.



Adding 3 indexes (with 3 commands)




mysql5.5> ALTER TABLE lien ADD KEY IdxPaysLangueCreation (Pays,Langue,Creation);

Query OK, 0 rows affected (1 min 7.24 sec)

Records: 0 Duplicates: 0 Warnings: 0


Total: 151.16 sec (4 % slower)


Of course, more data to moved implies a wider gap between the 2 methods




Deleting the index (with 3 commands)





Total: 1.77 sec










One Response to “ALTER Optimization”

  1. […] le 14/08/2012, il s’agit d’une question d’un collègue sur comment optimiser son ALTER TABLE, (mes excuses pour les soucis d’affichage, problème de compatibilité WordPress) […]

Leave a Reply