dasini.net – Diary of a MySQL expert

---

MySQL Tech Day @Paris

---

We’re happy to announce you that MySQL Tech Day will take place in Paris on Oct 10, 2013 in Oracle main office. It’ll be a full day event giving you an occasion to listen directly from Oracle developers about most of the improvements made recently within MySQL 5.6 and 5.7 development.

 

The agenda is the following :

• Overview: MySQL Innovation @Oracle

• MySQL Performance: Latest improvements in MySQL 5.6 & 5.7

• MySQL Optimizer: What is new, what is coming

• 50 tips to boost MySQL Performance

• MySQL Performance Schema: Overview / HOWTO

• MySQL Performance Schema: Hands-on (live lab)

 

Don’t miss such an opportunity to get most of the hot news about MySQL improvement directly from the source! It’ll be just two weeks after MySQL Connect, so there will be really a lot of news! So, book this date in your agenda right now and then don’t forget to register yourself for the event – attending is totally free, but places are limited, so registration is mandatory to secure enough seats.

 

To register : please, contact Alexis Bensa:

• by email: alexis.bensa(@)oracle.com

• or directly by phone: 01.57.60.27.96

 

Oracle main office address: Portes de La Defense 15, boulevard Charles de Gaulle.

 

Further details will be communicated over a time.

update (Oct 2): schedules

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: