{"id":101,"date":"2013-04-05T11:37:27","date_gmt":"2013-04-05T09:37:27","guid":{"rendered":"http:\/\/www.lafabriquedecode.com\/blog\/?p=101"},"modified":"2013-04-06T21:50:54","modified_gmt":"2013-04-06T19:50:54","slug":"mysql-poser-un-index-qui-penalise-le-temps-dexecution","status":"publish","type":"post","link":"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/","title":{"rendered":"MySQL : poser un index qui…p\u00e9nalise le temps d’ex\u00e9cution !"},"content":{"rendered":"

Avant de poser un index sur un (ou plusieurs) champs d’une table, il faut \u00e9valuer de la mani\u00e8re la plus pr\u00e9cise possible l’impact qu’aura cette pose sur le temps d’ex\u00e9cution des requ\u00eates. Lorsque les d\u00e9veloppeurs d\u00e9couvrent les index (j’en connais qui sont en poste depuis des ann\u00e9es et ne savent m\u00eame pas ce que c’est…), ils ont tendance \u00e0 en mettre de partout. Gare ! Un index pos\u00e9 \u00e0 tort peut p\u00e9naliser les s\u00e9lections, les mises \u00e0 jour\/suppressions\/insertions. Regardons un exemple tr\u00e8s simple; Henri a d\u00e9cid\u00e9 que puisque les index sont l\u00e0 pour acc\u00e9l\u00e9rer les recherches, il va en poser un sur sa table, dont voici le sch\u00e9ma (l’intention) :<\/p>\n

\r\n\r\ncreate table if not exists test_index\r\n(\r\n   id mediumint unsigned not null primary key,\r\n   nom char(4) not null,\r\n   valid tinyint unsigned not null\r\n);\r\n\r\n<\/pre>\n
Sa table comporte 100 000 tuples, qu’il ins\u00e8re avec une proc\u00e9dure stock\u00e9e :<\/p>\n
\r\nDROP PROCEDURE IF EXISTS insertion;\r\n\r\nDELIMITER \/\/\r\nCREATE PROCEDURE insertion()\r\nBEGIN\r\n    DECLARE i INT DEFAULT 1;\r\n\r\n    WHILE (i<=100000) DO\r\n        INSERT INTO test_index VALUES(i,'test', 1);\r\n        SET i=i+1;\r\n    END WHILE;\r\nEND\r\n\/\/\r\nCALL insertion();\r\n<\/pre>\n
Il part du principe (plut\u00f4t intelligent, au demeurant) que la requ\u00eate qu’il fait le plus souvent \u00e9tant :<\/p>\n
\r\n\r\nselect * from test_index where valid = 1;\r\n\r\n<\/pre>\n
Il serait judicieux qu’il pose un index sur valid :<\/p>\n
\r\n\r\nalter table test_index add index(valid);\r\n\r\n<\/pre>\n
Voil\u00e0 la pose r\u00e9alis\u00e9e ! Une fois l’index en place, il a quand m\u00eame le r\u00e9flexe de valider que la pose de cet index\u00a0acc\u00e9l\u00e8re\u00a0bien les recherches, comme il le souhaitait…<\/p>\n
Il a bien ses 100 000 enregistrements…<\/p>\n
\r\n\r\nmysql> select count(*) from test_index;\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n| 100000 |\r\n+----------+\r\n\r\n<\/pre>\n
Avant la pose de l’index, un EXPLAIN<\/em> sur sa requ\u00eate favorite donnait :<\/p>\n
\"snapshot2\"<\/a><\/p>\n
On voit au passage qu’il r\u00e9alisait un full table scan<\/em> (type = ALL, possible_keys = NULL), c’est \u00e0 dire qu’il parcourait l’ensemble des tuples de sa table (rows). Dor\u00e9navant, cela donne :<\/p>\n
\"snapshot2\"<\/p>\n
Henri constate avec satisfaction que la pose de son index lui fait lire moiti\u00e9 moins d’enregistrements (regardez la colonne rows<\/em>). Pourtant, lorsqu’il lance sa requ\u00eate, Henri est tr\u00e8s d\u00e9\u00e7u :<\/p>\n
\r\n\r\n100000 rows in set (0.19 sec)\r\n\r\n<\/pre>\n
Alors que lorsqu’il d\u00e9truit l’index, il obtient :<\/p>\n
\r\n\r\n100000 rows in set (0.10 sec)\r\n\r\n<\/pre>\n
Pour r\u00e9sumer, Henri lit moiti\u00e9 moins de tuples…en deux fois plus de temps ! Ce n’est pas du tout conforme \u00e0 l’id\u00e9e qu’il se faisait des index et ses espoirs sont d\u00e9\u00e7us ! En posant un index inutile sur un champ qui n’a qu’une valeur possible (1), Henri a pos\u00e9 un index qui p\u00e9nalise le temps d’ex\u00e9cution de sa requ\u00eate…Il aura tout de m\u00eame compris que le seul vrai moyen de v\u00e9rifier qu’on tire des b\u00e9n\u00e9fices d’un index est le benchmarking<\/em> des requ\u00eates qui l’utilisent.<\/p>\n","protected":false},"excerpt":{"rendered":"
Avant de poser un index sur un (ou plusieurs) champs d’une table, il faut \u00e9valuer de la mani\u00e8re la plus pr\u00e9cise possible l’impact qu’aura cette pose sur le temps d’ex\u00e9cution des requ\u00eates. Lorsque les d\u00e9veloppeurs d\u00e9couvrent les index (j’en connais qui sont en poste depuis des ann\u00e9es et ne savent m\u00eame pas ce que c’est…), […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[2],"tags":[11,10,6],"yoast_head":"\nMySQL : poser un index qui...p\u00e9nalise le temps d'ex\u00e9cution ! - La Fabrique de code - Tech blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL : poser un index qui...p\u00e9nalise le temps d'ex\u00e9cution ! - La Fabrique de code - Tech blog\" \/>\n<meta property=\"og:description\" content=\"Avant de poser un index sur un (ou plusieurs) champs d’une table, il faut \u00e9valuer de la mani\u00e8re la plus pr\u00e9cise possible l’impact qu’aura cette pose sur le temps d’ex\u00e9cution des requ\u00eates. Lorsque les d\u00e9veloppeurs d\u00e9couvrent les index (j’en connais qui sont en poste depuis des ann\u00e9es et ne savent m\u00eame pas ce que c’est…), […]\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/\" \/>\n<meta property=\"og:site_name\" content=\"La Fabrique de code - Tech blog\" \/>\n<meta property=\"article:published_time\" content=\"2013-04-05T09:37:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-04-06T19:50:54+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.lafabriquedecode.com\/blog\/wp-content\/uploads\/2013\/04\/snapshot23.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:creator\" content=\"@LaFabrique2Code\" \/>\n<meta name=\"twitter:site\" content=\"@LaFabrique2Code\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/\",\"url\":\"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/\",\"name\":\"MySQL : poser un index qui...p\u00e9nalise le temps d'ex\u00e9cution ! - La Fabrique de code - Tech blog\",\"isPartOf\":{\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/#website\"},\"datePublished\":\"2013-04-05T09:37:27+00:00\",\"dateModified\":\"2013-04-06T19:50:54+00:00\",\"author\":{\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/83863c048b82fd9ccf6407bddd241162\"},\"breadcrumb\":{\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"http:\/\/www.lafabriquedecode.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL : poser un index qui…p\u00e9nalise le temps d’ex\u00e9cution !\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/#website\",\"url\":\"http:\/\/www.lafabriquedecode.com\/blog\/\",\"name\":\"La Fabrique de code - Tech blog\",\"description\":\"PHP objet, MySQL, Design Patterns, OOP...et plus !\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.lafabriquedecode.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/83863c048b82fd9ccf6407bddd241162\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/0.gravatar.com\/avatar\/fc2e1de7c8a1871b50ff9c6a6f8682a2?s=96&d=retro&r=g\",\"contentUrl\":\"http:\/\/0.gravatar.com\/avatar\/fc2e1de7c8a1871b50ff9c6a6f8682a2?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"url\":\"http:\/\/www.lafabriquedecode.com\/blog\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL : poser un index qui...p\u00e9nalise le temps d'ex\u00e9cution ! - La Fabrique de code - Tech blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/","og_locale":"fr_FR","og_type":"article","og_title":"MySQL : poser un index qui...p\u00e9nalise le temps d'ex\u00e9cution ! - La Fabrique de code - Tech blog","og_description":"Avant de poser un index sur un (ou plusieurs) champs d’une table, il faut \u00e9valuer de la mani\u00e8re la plus pr\u00e9cise possible l’impact qu’aura cette pose sur le temps d’ex\u00e9cution des requ\u00eates. Lorsque les d\u00e9veloppeurs d\u00e9couvrent les index (j’en connais qui sont en poste depuis des ann\u00e9es et ne savent m\u00eame pas ce que c’est…), […]","og_url":"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/","og_site_name":"La Fabrique de code - Tech blog","article_published_time":"2013-04-05T09:37:27+00:00","article_modified_time":"2013-04-06T19:50:54+00:00","og_image":[{"url":"http:\/\/www.lafabriquedecode.com\/blog\/wp-content\/uploads\/2013\/04\/snapshot23.png"}],"author":"admin","twitter_card":"summary","twitter_creator":"@LaFabrique2Code","twitter_site":"@LaFabrique2Code","twitter_misc":{"\u00c9crit par":"admin","Dur\u00e9e de lecture estim\u00e9e":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/","url":"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/","name":"MySQL : poser un index qui...p\u00e9nalise le temps d'ex\u00e9cution ! - La Fabrique de code - Tech blog","isPartOf":{"@id":"http:\/\/www.lafabriquedecode.com\/blog\/#website"},"datePublished":"2013-04-05T09:37:27+00:00","dateModified":"2013-04-06T19:50:54+00:00","author":{"@id":"http:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/83863c048b82fd9ccf6407bddd241162"},"breadcrumb":{"@id":"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.lafabriquedecode.com\/blog\/2013\/04\/mysql-poser-un-index-qui-penalise-le-temps-dexecution\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"http:\/\/www.lafabriquedecode.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL : poser un index qui…p\u00e9nalise le temps d’ex\u00e9cution !"}]},{"@type":"WebSite","@id":"http:\/\/www.lafabriquedecode.com\/blog\/#website","url":"http:\/\/www.lafabriquedecode.com\/blog\/","name":"La Fabrique de code - Tech blog","description":"PHP objet, MySQL, Design Patterns, OOP...et plus !","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.lafabriquedecode.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Person","@id":"http:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/83863c048b82fd9ccf6407bddd241162","name":"admin","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"http:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/image\/","url":"http:\/\/0.gravatar.com\/avatar\/fc2e1de7c8a1871b50ff9c6a6f8682a2?s=96&d=retro&r=g","contentUrl":"http:\/\/0.gravatar.com\/avatar\/fc2e1de7c8a1871b50ff9c6a6f8682a2?s=96&d=retro&r=g","caption":"admin"},"url":"http:\/\/www.lafabriquedecode.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/posts\/101"}],"collection":[{"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/comments?post=101"}],"version-history":[{"count":16,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/posts\/101\/revisions"}],"predecessor-version":[{"id":133,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/posts\/101\/revisions\/133"}],"wp:attachment":[{"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/media?parent=101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/categories?post=101"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/tags?post=101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}