{"id":1261,"date":"2012-05-30T10:20:20","date_gmt":"2012-05-30T09:20:20","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1261"},"modified":"2012-05-30T10:23:08","modified_gmt":"2012-05-30T09:23:08","slug":"optimiser-un-alter","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2012\/05\/30\/optimiser-un-alter\/","title":{"rendered":"Optimiser un ALTER"},"content":{"rendered":"<p>Lors d&rsquo;une discussion \u00e0 la caf\u00e8t, la question suivante fut pos\u00e9e\u00a0: \u00ab\u00a0<span style=\"color: #000080;\">Faire un ALTER TABLE avec plusieurs instructions est il plus rapide qu&rsquo;un ALTER TABLE par instruction<\/span>\u00a0\u00bb\u00a0?<\/p>\n<p>Les 2 protagonistes n&rsquo;\u00e9tant pas d&rsquo;accord entre eux, c&rsquo;est tout naturellement que je fus invit\u00e9 \u00e0 donner mon avis.<\/p>\n<p>La r\u00e9ponse me semble assez intuitive, mais comme des chiffres valent mieux qu&rsquo;un longs discours&#8230;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Le contexte<\/span>\u00a0:<\/p>\n<ul>\n<li>MySQL Community Server 5.5.22<\/li>\n<li>Une table InnoDB de 8 539 238 enregistrements<\/li>\n<li>17 colonnes avec INT, CHAR, VARCHAR, TEXT, DATETIME, &#8230;<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Ajout de 3 index<\/span> (en une seule commande)<\/p>\n<p>&nbsp;<\/p>\n<pre>mysql5.5&gt; ALTER TABLE lien<\/pre>\n<pre>-&gt; ADD KEY `IdxIdMembre` (`IdMembre`),<\/pre>\n<pre>-&gt; ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100)),<\/pre>\n<pre>-&gt; ADD KEY `IdxPaysLangueCreation` (`Pays`,`Langue`,`Creation`);<\/pre>\n<pre>Query OK, 0 rows affected (<strong>2 min 26.43 sec<\/strong>)<\/pre>\n<pre>Records: 0 Duplicates: 0 Warnings: 0<\/pre>\n<p>&nbsp;<\/p>\n<p>Ce qui fait un total de <span style=\"color: #ff0000;\"><strong>146.43<\/strong><\/span> sec<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Suppression de index<\/span> (en une seule commande)<\/p>\n<p>&nbsp;<\/p>\n<pre>mysql5.5&gt; ALTER TABLE lien<\/pre>\n<pre>-&gt; DROP KEY `IdxIdMembre`,<\/pre>\n<pre>-&gt; DROP KEY `IdxCreationTitre`,<\/pre>\n<pre>-&gt; DROP KEY `IdxPaysLangueCreation`;<\/pre>\n<pre>Query OK, 0 rows affected (<span style=\"color: #355e00;\"><strong>1.78 sec<\/strong><\/span>)<\/pre>\n<pre>Records: 0 Duplicates: 0 Warnings: 0<\/pre>\n<p>&nbsp;<\/p>\n<p>Au passage on peut remarquer que depuis MySQL 5.5, supprimer des index secondaires (non cl\u00e9 primaire) ne co\u00fbte rien.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Ajout de 3 index<\/span> (en 3 commandes)<\/p>\n<p>&nbsp;<\/p>\n<pre>mysql5.5&gt; ALTER TABLE lien ADD KEY `IdxIdMembre` (`IdMembre`);<\/pre>\n<pre>Query OK, 0 rows affected (<strong>26.34 sec<\/strong>)<\/pre>\n<pre>Records: 0 Duplicates: 0 Warnings: 0<\/pre>\n<p>&nbsp;<\/p>\n<pre>mysql5.5&gt; ALTER TABLE lien ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100));<\/pre>\n<pre>Query OK, 0 rows affected (<strong>57.58 sec<\/strong>)<\/pre>\n<pre>Records: 0 Duplicates: 0 Warnings: 0<\/pre>\n<p>&nbsp;<\/p>\n<pre>mysql5.5&gt; ALTER TABLE lien ADD KEY `IdxPaysLangueCreation` (`Pays`,`Langue`,`Creation`);<\/pre>\n<pre>Query OK, 0 rows affected (<strong>1 min 7.24 sec<\/strong>)<\/pre>\n<pre>Records: 0 Duplicates: 0 Warnings: 0<\/pre>\n<p>&nbsp;<\/p>\n<p>Ce qui fait un total de <span style=\"color: #ff0000;\"><strong>151.16 <\/strong><\/span>sec (<strong>4\u00a0% plus lent<\/strong>)<\/p>\n<p>&nbsp;<\/p>\n<p>Bien entendu, plus le volume de donn\u00e9e \u00e0 d\u00e9placer est important, plus l&rsquo;\u00e9cart entre les 2 fa\u00e7ons de faire risque d&rsquo;\u00eatre important.<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Suppression de index<\/span> (en 3 commandes)<\/p>\n<p>&nbsp;<\/p>\n<pre>mysql5.5&gt; ALTER TABLE lien Drop index `IdxIdMembre`;<\/pre>\n<pre>Query OK, 0 rows affected (<strong>0.29 sec<\/strong>)<\/pre>\n<pre>Records: 0 Duplicates: 0 Warnings: 0<\/pre>\n<p>&nbsp;<\/p>\n<pre>mysql5.5&gt; ALTER TABLE lien DROP KEY `IdxCreationTitre`;<\/pre>\n<pre>Query OK, 0 rows affected (<strong>1.06 sec<\/strong>)<\/pre>\n<pre>Records: 0 Duplicates: 0 Warnings: 0<\/pre>\n<p>&nbsp;<\/p>\n<pre>mysql5.5&gt; ALTER TABLE lien DROP KEY `IdxPaysLangueCreation`;<\/pre>\n<pre>Query OK, 0 rows affected (<strong>0.42 sec<\/strong>)<\/pre>\n<pre>Records: 0 Duplicates: 0 Warnings: 0<\/pre>\n<p>&nbsp;<\/p>\n<p>Total: <span style=\"color: #355e00;\"><strong>1.77 sec<\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<p>CQFD<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Divers<\/span><\/p>\n<p>&nbsp;<\/p>\n<pre>CREATE TABLE `lien` (<\/pre>\n<pre>`id` int(11) unsigned NOT NULL AUTO_INCREMENT,<\/pre>\n<pre>...<\/pre>\n<pre>PRIMARY KEY (`id`)<\/pre>\n<pre>) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<pre>show table status like 'lien'\\G<\/pre>\n<pre>*************** 1. row ***************<\/pre>\n<pre>Name: lien<\/pre>\n<pre>Engine: InnoDB<\/pre>\n<pre>Version: 10<\/pre>\n<pre>Row_format: Compact<\/pre>\n<pre>Rows: 8427138<\/pre>\n<pre>Avg_row_length: 609<\/pre>\n<pre>Data_length: 5139070976<\/pre>\n<pre>Max_data_length: 0<\/pre>\n<pre>Index_length: 0<\/pre>\n<pre>Data_free: 4194304<\/pre>\n<pre>Auto_increment: 8901321<\/pre>\n<pre>...<\/pre>\n<pre>Collation: utf8_swedish_ci<\/pre>\n<pre>Checksum: NULL<\/pre>\n<pre>Create_options:<\/pre>\n<pre>Comment:<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Lors d&rsquo;une discussion \u00e0 la caf\u00e8t, la question suivante fut pos\u00e9e : \u00ab Faire un ALTER TABLE avec plusieurs instructions est il plus rapide qu&rsquo;un ALTER TABLE par instruction \u00bb ?<\/p>\n<p>Les 2 protagonistes n&rsquo;\u00e9tant pas d&rsquo;accord entre eux, c&rsquo;est tout naturellement que je fus invit\u00e9 \u00e0 donner mon avis.<\/p>\n<p>La r\u00e9ponse me semble assez intuitive, mais comme des chiffres valent mieux qu&rsquo;un longs discours&#8230;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[88,81,34],"tags":[],"class_list":["post-1261","post","type-post","status-publish","format-standard","hentry","category-astuce","category-bench","category-optimisation"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-kl","jetpack-related-posts":[{"id":453,"url":"https:\/\/dasini.net\/blog\/2009\/02\/18\/optimisation-de-requetes-comprendre-loptimiseur-de-mysql\/","url_meta":{"origin":1261,"position":0},"title":"Optimisation de requ\u00eates: comprendre l&rsquo;optimiseur de MySQL","author":"Olivier DASINI","date":"18 f\u00e9vrier 2009","format":false,"excerpt":"Le but de cet article est d'optimiser une simple requ\u00eate (SELECT avg(Population) FROM city GROUP BY CountryCode) et surtout de comprendre comment l'optimiseur proc\u00e8de, en \u00e9tudiant les r\u00e9sultats donn\u00e9s par les variables qui permettent de surveiller le serveur MySQL.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1093,"url":"https:\/\/dasini.net\/blog\/2011\/08\/01\/mariadb-5-3-progress-reporting\/","url_meta":{"origin":1261,"position":1},"title":"MariaDB 5.3, Progress reporting","author":"Olivier DASINI","date":"1 ao\u00fbt 2011","format":false,"excerpt":"Parmi les nouveaut\u00e9s de MariaDB 5.3, l'une bien pratique est le \"progress reporting\", qui permet de savoir o\u00f9 le serveur en est dans l'ex\u00e9cution d'une commande. Le principe est simple, j'ex\u00e9cute une commande et MariaDB m'indique \u00e0 le pourcentage effectu\u00e9 pour une \u00e9tape donn\u00e9e. Un exemple: MariaDB-5.3> ALTER TABLE client\u2026","rel":"","context":"Dans &quot;Astuce&quot;","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":295,"url":"https:\/\/dasini.net\/blog\/2008\/12\/06\/les-nouveautes-de-mysql-51-part-35\/","url_meta":{"origin":1261,"position":2},"title":"Les nouveaut\u00e9s de MySQL 5.1 &#8212; (part 3\/5)","author":"Olivier DASINI","date":"6 d\u00e9cembre 2008","format":false,"excerpt":"MySQL propose une solution de haute disponibilit\u00e9 : MySQL Cluster. Cette technologie permet de mettre en place une architecture cluster shared nothing \u00e0 l'aide de tables au format NDBCluster. Jusqu'\u00e0 MySQL 5.0, MySQL Cluster ne fonctionnait qu'enti\u00e8rement en m\u00e9moire. Ceci excluait donc certaines bases de donn\u00e9es trop volumineuses. La nouvelle\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1557,"url":"https:\/\/dasini.net\/blog\/2015\/11\/30\/json-et-colonnes-generees-avec-mysql\/","url_meta":{"origin":1261,"position":3},"title":"JSON et colonnes g\u00e9n\u00e9r\u00e9es avec MySQL","author":"Olivier DASINI","date":"30 novembre 2015","format":false,"excerpt":"Le 24 novembre dernier, lors du Forum PHP, Tomas Ulin (Oracle's MySQL VP of Engineering) a parl\u00e9 de l'utilisation de JSON dans MySQL \"MySQL 5.7 & JSON: New opportunities for developers\". Voici les r\u00e9ponses \u00e0 quelques questions qui m'ont \u00e9t\u00e9 pos\u00e9es: Comment se comporte mysqldump avec les colonnes g\u00e9n\u00e9r\u00e9es ?\u2026","rel":"","context":"Dans &quot;json&quot;","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json-fr\/"},"img":{"alt_text":"JSON","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/json_icon.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1533,"url":"https:\/\/dasini.net\/blog\/2015\/11\/17\/30-mins-avec-json-en-mysql\/","url_meta":{"origin":1261,"position":4},"title":"30 mins avec JSON en MySQL","author":"Olivier DASINI","date":"17 novembre 2015","format":false,"excerpt":"Comme vous le savez MySQL 5.7 est GA. Cette nouvelle mouture de la base de donn\u00e9es open source la plus populaire au monde a plus de 150 nouvelles fonctionnalit\u00e9s. L'une d'entre elle est un type de donn\u00e9es JSON natif ainsi que les fonctions JSON associ\u00e9es. Prenons 30 minutes pour voir\u2026","rel":"","context":"Dans &quot;json&quot;","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json-fr\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":209,"url":"https:\/\/dasini.net\/blog\/2008\/11\/26\/mysql-5-les-vues-part-27\/","url_meta":{"origin":1261,"position":5},"title":"MySQL 5 : Les vues &#8212; (part 2\/7)","author":"Olivier DASINI","date":"26 novembre 2008","format":false,"excerpt":"Syntaxe d'une vue CREATE VIEW La commande MySQL pour cr\u00e9er une vue est assez proche de la syntaxe du standard SQL. CREATE VIEW nom_de_la_vue AS requ\u00eate_select","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1261","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=1261"}],"version-history":[{"count":6,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1261\/revisions"}],"predecessor-version":[{"id":1267,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1261\/revisions\/1267"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1261"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}