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

 

QED

 

 

Divers

 

 

 

 

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