ALTER Optimization
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)
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql5.5> ALTER TABLE lien -> ADD KEY `IdxIdMembre` (`IdMembre`), -> ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100)), -> ADD KEY `IdxPaysLangueCreation` (`Pays`,`Langue`,`Creation`); Query OK, 0 rows affected (<strong>2 min 26.43 sec</strong>) Records: 0 Duplicates: 0 Warnings: 0 |
Total: 146.43 sec
Deleting the index (with a single command)
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql5.5> ALTER TABLE lien -> DROP KEY `IdxIdMembre`, -> DROP KEY `IdxCreationTitre`, -> DROP KEY `IdxPaysLangueCreation`; Query OK, 0 rows affected (<span style="color: #355e00;"><strong>1.78 sec</strong></span>) Records: 0 Duplicates: 0 Warnings: 0 |
Note: With MySQL 5.5, delete secondary indexes (not PK) is costless.
Adding 3 indexes (with 3 commands)
|
1 2 3 4 5 |
mysql5.5> ALTER TABLE lien ADD KEY `IdxIdMembre` (`IdMembre`); Query OK, 0 rows affected (<strong>26.34 sec</strong>) Records: 0 Duplicates: 0 Warnings: 0 |
|
1 2 3 4 5 |
mysql5.5> ALTER TABLE lien ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100)); Query OK, 0 rows affected (<strong>57.58 sec</strong>) Records: 0 Duplicates: 0 Warnings: 0 |
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)
|
1 2 3 4 5 |
mysql5.5> ALTER TABLE lien Drop index `IdxIdMembre`; Query OK, 0 rows affected (<strong>0.29 sec</strong>) Records: 0 Duplicates: 0 Warnings: 0 |
|
1 2 3 4 5 |
mysql5.5> ALTER TABLE lien DROP KEY `IdxCreationTitre`; Query OK, 0 rows affected (<strong>1.06 sec</strong>) Records: 0 Duplicates: 0 Warnings: 0 |
|
1 2 3 4 5 |
mysql5.5> ALTER TABLE lien DROP KEY `IdxPaysLangueCreation`; Query OK, 0 rows affected (<strong>0.42 sec</strong>) Records: 0 Duplicates: 0 Warnings: 0 |
Total: 1.77 sec
QED
Divers
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
CREATE TABLE `lien` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `Url` varchar(330) COLLATE utf8_swedish_ci NOT NULL, `Titre` text COLLATE utf8_swedish_ci NOT NULL, `IdMembre` int(11) NOT NULL, `Langue` char(2) COLLATE utf8_swedish_ci NOT NULL DEFAULT 'fr', `Pays` char(2) COLLATE utf8_swedish_ci DEFAULT NULL, `Encoding` varchar(32) COLLATE utf8_swedish_ci DEFAULT NULL, `Affiliate` int(11) DEFAULT NULL, `PictureUrl` varchar(512) COLLATE utf8_swedish_ci DEFAULT NULL, `Overview` text COLLATE utf8_swedish_ci, `hasIllustration` tinyint(1) DEFAULT '0', `EndDate` date DEFAULT NULL, `Creation` datetime DEFAULT '2008-10-27 00:00:00', `Visible` tinyint(1) DEFAULT '0', `Keywords` text COLLATE utf8_swedish_ci, `Source` int(1) NOT NULL DEFAULT '0', `DomainId` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8901321 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
show table status like 'lien'\G *************************** 1. row *************************** Name: lien Engine: InnoDB Version: 10 Row_format: Compact Rows: 8427138 Avg_row_length: 609 Data_length: 5139070976 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: 8901321 Create_time: 2012-05-29 14:56:48 Update_time: NULL Check_time: NULL Collation: utf8_swedish_ci Checksum: NULL Create_options: Comment: |
MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
[…] 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) […]