dasini.net – Diary of a MySQL expert

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)

 

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 (2 min 26.43 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

Total: 146.43 sec

 

 

Deleting the index (with a single command)

 

mysql5.5> ALTER TABLE lien

-> DROP KEY `IdxIdMembre`,

-> DROP KEY `IdxCreationTitre`,

-> DROP KEY `IdxPaysLangueCreation`;

Query OK, 0 rows affected (1.78 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

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 `IdxIdMembre` (`IdMembre`);

Query OK, 0 rows affected (26.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

mysql5.5> ALTER TABLE lien ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100));

Query OK, 0 rows affected (57.58 sec)

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)

 

mysql5.5> ALTER TABLE lien Drop index `IdxIdMembre`;

Query OK, 0 rows affected (0.29 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

mysql5.5> ALTER TABLE lien DROP KEY `IdxCreationTitre`;

Query OK, 0 rows affected (1.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

mysql5.5> ALTER TABLE lien DROP KEY `IdxPaysLangueCreation`;

Query OK, 0 rows affected (0.42 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

Total: 1.77 sec

 

QED

 

 

Divers

 

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

 

 

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:

 

Filed under:
MySQL by Olivier DASINI