{"id":1256,"date":"2012-05-15T13:57:29","date_gmt":"2012-05-15T12:57:29","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1256"},"modified":"2016-03-02T10:49:40","modified_gmt":"2016-03-02T09:49:40","slug":"jointure-vs-sous-requete","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2012\/05\/15\/jointure-vs-sous-requete\/","title":{"rendered":"Jointure vs sous-requ\u00eate"},"content":{"rendered":"

MySQL est connu pour ne pas \u00eatre tr\u00e8s performant avec les sous-requ\u00eates. Ce n’est pas faux, et d’ailleurs c’est encore le cas avec MySQL 5.5. Le contournement consiste en g\u00e9n\u00e9ral \u00e0 r\u00e9\u00e9crire la requ\u00eate, certaines sous-requ\u00eates pouvant \u00eatre ais\u00e9ment r\u00e9\u00e9crite en jointure.<\/p>\n

C’est le cas de
\nSELECT a FROM T1 WHERE col IN (SELECT col FROM T2…)\u00a0 qui se transforme en
\nSELECT distinct a FROM T1 INNER JOIN T2 ON TI.col=T2.col WHERE \u2026
\nAvec un exemple concret, il est possible de faire les constatations suivantes.<\/p>\n

Serveur\u00a0: MySQL: 5.5.22 Community Server (GPL)<\/h2>\n

 <\/p>\n

Requ\u00eate 1\u00a0:<\/p>\n

SELECT count(*) FROM customer<\/pre>\n
WHERE id_cutomer IN<\/pre>\n
(<\/pre>\n
    SELECT id_cutomer FROM customer<\/pre>\n
    WHERE phone like '06%'<\/pre>\n
);<\/pre>\n
 <\/p>\n
 <\/p>\n
Requ\u00eate 2 (requ\u00eate 1 r\u00e9\u00e9crite avec une jointure)\u00a0:<\/p>\n
SELECT count(*) FROM customer c1 INNER JOIN\r\n    customer c2 USING(id_cutomer)\r\nWHERE c2.phone like '06%';\r\n<\/pre>\n
 <\/p>\n
P.S. Le crit\u00e8re de l’auto-jointure \u00e9tant sur toutes les parties de l’index unique (la cl\u00e9 primaire), il n’ est donc pas n\u00e9cessaire de d\u00e9boublonner avec DISTINCT (ou GROUP BY).<\/p>\n
 <\/p>\n
 <\/p>\n
Apr\u00e8s benchs, l’\u00e9cart entre ses 2 requ\u00eates est d’environ 7\u00a0%. Dans la vraie vie, l’\u00e9cart peut aller de quelques secondes \u00e0 une requ\u00eate qui ne rend jamais la main…<\/p>\n
 <\/p>\n
La lecture du plan d\u2019ex\u00e9cution (QEP) donn\u00e9 par la commande EXPLAIN, n’explique pas de mani\u00e8re flagrante la diff\u00e9rence de performance.<\/p>\n
Requ\u00eate 1\u00a0:<\/p>\n
*************************** 1. row ***************************<\/pre>\n
id: 1<\/pre>\n
select_type: PRIMARY<\/pre>\n
table: customer<\/pre>\n
type: index<\/strong><\/pre>\n
possible_keys: NULL<\/pre>\n
key: idx_lname<\/strong><\/pre>\n
key_len: 50<\/pre>\n
ref: NULL<\/pre>\n
rows: 700021<\/strong><\/pre>\n
Extra: Using where; Using index<\/strong><\/pre>\n
*************************** 2. row ***************************<\/pre>\n
id: 2<\/pre>\n
select_type: DEPENDENT SUBQUERY<\/strong><\/pre>\n
table: customer<\/p>\n
type: unique_subquery<\/strong><\/p>\n
possible_keys: PRIMARY<\/p>\n
key: PRIMARY<\/strong><\/p>\n
key_len: 4<\/p>\n
ref: func<\/strong><\/p>\n
rows: 1<\/p>\n
Extra: Using where<\/strong><\/p>\n
 <\/p>\n
Requ\u00eate 2\u00a0:<\/p>\n
*************************** 1. row ***************************<\/pre>\n
id: 1<\/p>\n
select_type: SIMPLE<\/p>\n
table: c1<\/p>\n
type: index<\/strong><\/p>\n
possible_keys: PRIMARY<\/p>\n
key: idx_lname<\/strong><\/p>\n
key_len: 50<\/p>\n
ref: NULL<\/p>\n
rows: 700021<\/strong><\/p>\n
Extra: Using index<\/strong><\/p>\n
*************************** 2. row ***************************<\/p>\n
id: 1<\/p>\n
select_type: SIMPLE<\/strong><\/p>\n
table: c2<\/p>\n
type: eq_ref<\/strong><\/p>\n
possible_keys: PRIMARY<\/p>\n
key: PRIMARY<\/strong><\/p>\n
key_len: 4<\/p>\n
ref: customer.c1.id_cutomer<\/strong><\/p>\n
rows: 1<\/p>\n
Extra: Using where<\/strong><\/p>\n
 <\/p>\n
 <\/p>\n
Qu’en est il de la version 5.6 de MySQL\u00a0?<\/p>\n
Cette derni\u00e8re mouture apporte une ribambelle d’am\u00e9lioration, notamment en ce qui concerne l’optimisation des sous-requ\u00eates…<\/p>\n
 <\/p>\n
Serveur\u00a0: MySQL: 5.6.5-m8 MySQL Community Server (GPL)<\/h2>\n
 <\/p>\n
Les QEPs respectifs sont<\/p>\n
*************************** 1. row ***************************<\/pre>\n
id: 1<\/p>\n
select_type: SIMPLE<\/p>\n
table: customer<\/p>\n
type: index<\/p>\n
possible_keys: PRIMARY<\/p>\n
key: idx_lname<\/p>\n
key_len: 50<\/p>\n
ref: NULL<\/p>\n
rows: 699628<\/p>\n
Extra: Using index<\/p>\n
*************************** 2. row ***************************<\/p>\n
id: 1<\/p>\n
select_type: SIMPLE<\/p>\n
table: customer<\/p>\n
type: eq_ref<\/p>\n
possible_keys: PRIMARY<\/p>\n
key: PRIMARY<\/p>\n
key_len: 4<\/p>\n
ref: customer.customer.id_cutomer<\/p>\n
rows: 1<\/p>\n
Extra: Using where<\/p>\n
 <\/p>\n
et<\/p>\n
 <\/p>\n
*************************** 1. row ***************************<\/pre>\n
id: 1<\/p>\n
select_type: SIMPLE<\/p>\n
table: c1<\/p>\n
type: index<\/p>\n
possible_keys: PRIMARY<\/p>\n
key: idx_lname<\/p>\n
key_len: 50<\/p>\n
ref: NULL<\/p>\n
rows: 699628<\/p>\n
Extra: Using index<\/p>\n
*************************** 2. row ***************************<\/p>\n
id: 1<\/p>\n
select_type: SIMPLE<\/p>\n
table: c2<\/p>\n
type: eq_ref<\/p>\n
possible_keys: PRIMARY<\/p>\n
key: PRIMARY<\/p>\n
key_len: 4<\/p>\n
ref: customer.c1.id_cutomer<\/p>\n
rows: 1<\/p>\n
Extra: Using where<\/p>\n
 <\/p>\n
 <\/p>\n
Oui, oui vous avez bien lu, ils sont bien (quasiment<\/em>) identique\u00a0!<\/p>\n
En d\u00e9taillant le QEP de la sous-requ\u00eate (avec EXPLAIN EXTENDED), on constate\u00a0que l’optimiseur \u00e0 (enfin) transform\u00e9 la sous-requ\u00eate en jointure :<\/p>\n
 <\/p>\n
 <\/p>\n
show warnings\\G<\/p>\n
*************************** 1. row ***************************<\/p>\n
Level: Note<\/p>\n
Code: 1003<\/p>\n
Message: \/* select#1 *\/ select count(0) AS `count(*)` from `customer`.`customer` join<\/span> `customer`.`customer`<\/strong> where ((`customer`.`customer`.`id_cutomer` = `customer`.`customer`.`id_cutomer`) and (`customer`.`customer`.`phone` like ‘06%’))<\/p>\n
 <\/p>\n
 <\/p>\n
C’est une bien bonne nouvelle\u00a0!<\/p>\n
Malheureusement MySQL 5.6 n’est pas encore en GA (fin d’ann\u00e9e \u00e0 priori selon mes sources). Mais tout n’est pas perdu\u00a0! (\u00e7a s’est du suspens :D).<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Quelques chiffres<\/h2>\n
 <\/p>\n
Un \u00ab\u00a0quick bench\u00a0\u00bb, avec mysqlslap <\/a><\/p>\n
 <\/p>\n
Requ\u00eate 1<\/span>\u00a0:<\/p>\n
mysqlslap –create-schema=test -q\u00a0\u00bbSELECT count(*) FROM customer WHERE id_cutomer IN (SELECT id_cutomer FROM cust WHERE phone like ‘06%’);\u00a0\u00bb -i200<\/p>\n
 <\/p>\n
Requ\u00eate 2<\/span>\u00a0:<\/p>\n
mysqlslap –create-schema=test -q\u00a0\u00bbSELECT count(*) FROM customer c1 INNER JOIN customer c2 USING(id_cutomer) WHERE c2.phone like ‘06%’;\u00a0\u00bb -i200<\/p>\n
 <\/p>\n
 <\/p>\n
MySQL 5.5<\/strong><\/p>\n
 <\/p>\n
Requ\u00eate 1<\/span>\u00a0:<\/p>\n
Minimum number of seconds to run all queries: 0.923 seconds<\/strong><\/p>\n
 <\/p>\n
Requ\u00eate 2<\/span>\u00a0:<\/p>\n
Minimum number of seconds to run all queries: 0.844 seconds<\/strong><\/p>\n
 <\/p>\n
 <\/p>\n
MySQL 5.6<\/strong><\/p>\n
 <\/p>\n
Requ\u00eate 1<\/span>\u00a0:<\/p>\n
Minimum number of seconds to run all queries: 0.974 seconds<\/strong><\/p>\n
 <\/p>\n
Requ\u00eate 2<\/span>\u00a0:<\/p>\n
Minimum number of seconds to run all queries: 0.979 seconds<\/strong><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Divers<\/h2>\n
CREATE TABLE `customer` (<\/pre>\n
`id_cutomer` int(11) NOT NULL AUTO_INCREMENT,<\/pre>\n
`fname` varchar(16) COLLATE utf8_swedish_ci NOT NULL DEFAULT '',<\/pre>\n
`lname` varchar(16) COLLATE utf8_swedish_ci NOT NULL DEFAULT '',<\/pre>\n
`street_1` varchar(20) COLLATE utf8_swedish_ci DEFAULT NULL,<\/pre>\n
`city` varchar(20) COLLATE utf8_swedish_ci DEFAULT NULL,<\/pre>\n
`state` char(2) COLLATE utf8_swedish_ci DEFAULT NULL,<\/pre>\n
`zip` varchar(9) COLLATE utf8_swedish_ci DEFAULT NULL,<\/pre>\n
`phone` varchar(16) COLLATE utf8_swedish_ci DEFAULT NULL,<\/pre>\n
`since` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,<\/pre>\n
`divers` text COLLATE utf8_swedish_ci,<\/pre>\n
PRIMARY KEY (`id_cutomer`),<\/pre>\n
KEY `idx_lname` (`lname`),<\/pre>\n
KEY `idx_city` (`city`),<\/pre>\n
KEY `idx_fname` (`fname`)<\/pre>\n
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci<\/pre>\n","protected":false},"excerpt":{"rendered":"
MySQL est connu pour ne pas \u00eatre tr\u00e8s performant avec les sous-requ\u00eates. Ce n’est pas faux, et d’ailleurs c’est encore le cas avec MySQL 5.5. Le contournement consiste en g\u00e9n\u00e9ral \u00e0 r\u00e9\u00e9crire la requ\u00eate, certaines sous-requ\u00eates pouvant \u00eatre ais\u00e9ment r\u00e9\u00e9crite en jointure.<\/p>\n
C’est le cas de<\/p>\n
SELECT a FROM T1 WHERE col IN (SELECT col FROM T2…)  qui se transforme en<\/p>\n
SELECT distinct a FROM T1 INNER JOIN T2 ON TI.col=T2.col WHERE \u2026<\/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,8],"tags":[184,186,185],"class_list":["post-1256","post","type-post","status-publish","format-standard","hentry","category-astuce","category-bench","category-mysql","tag-jointure","tag-mysql-5-6","tag-sous-requete"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-kg","jetpack-related-posts":[{"id":1425,"url":"https:\/\/dasini.net\/blog\/2013\/05\/27\/utiliser-une-sous-requete-cest-mal-suite-part-1-3\/","url_meta":{"origin":1256,"position":0},"title":"Utiliser une sous requ\u00eate c\u2019est mal ? (suite) part 1-3","author":"Olivier DASINI","date":"27 mai 2013","format":false,"excerpt":"Comme promit, voici la suite de l\u2019article Utiliser une sous-requ\u00eate c\u2019est mal ? L\u2019id\u00e9e ici est de r\u00e9pondre aux interrogations de svar et d\u2019en profiter pour explorer les nouvelles possibilit\u00e9s de la variante stable de MySQL qui poss\u00e8de l\u2019optimiseur le plus avanc\u00e9, c\u2019est \u00e0 dire MariaDB 5.5. Pr\u00e9ambule En pr\u00e9-requis,\u2026","rel":"","context":"Dans "MariaDB"","block_context":{"text":"MariaDB","link":"https:\/\/dasini.net\/blog\/category\/mariadb\/"},"img":{"alt_text":"","src":"https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA","width":350,"height":200,"srcset":"https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA 1x, https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA 1.5x"},"classes":[]},{"id":1593,"url":"https:\/\/dasini.net\/blog\/2016\/03\/02\/30-mins-avec-mysql-query-rewriter\/","url_meta":{"origin":1256,"position":1},"title":"30 mins avec MySQL Query Rewriter","author":"Olivier DASINI","date":"2 mars 2016","format":false,"excerpt":"Parfois des requ\u00eates probl\u00e9matiques tournent sur le serveur, mais il n'est pas possible de r\u00e9gler le probl\u00e8me \u00e0 la source (Requ\u00eates venant d'un ORM par example) MySQL 5.7 fournit une API pre et post parse query rewrite. Les utilisateurs peuvent \u00e9crire leurs propre plugins ce qui permet d'\u00e9liminer le besoin\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1413,"url":"https:\/\/dasini.net\/blog\/2013\/04\/23\/utiliser-une-sous-requete-cest-mal\/","url_meta":{"origin":1256,"position":2},"title":"Utiliser une sous-requ\u00eate c’est mal ?","author":"Olivier DASINI","date":"23 avril 2013","format":false,"excerpt":"Jusqu\u2019en MySQL 5.5 inclus, l\u2019utilisation de sous-requ\u00eates peut, dans certain cas, \u00eatre la cause de probl\u00e8mes de performances (l\u2019optimiseur est bien meilleur en MySQL 5.6, MariaDB 5.5 et MariaDB 10). R\u00e9cemment j\u2019ai eu un souci en prod, apr\u00e8s une MEP, avec une requ\u00eate qui durait en moyenne plus de 1000\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":350,"url":"https:\/\/dasini.net\/blog\/2008\/12\/19\/mysql-5-les-vues-part-57\/","url_meta":{"origin":1256,"position":3},"title":"MySQL 5 : Les vues — (part 5\/7)","author":"Olivier DASINI","date":"19 d\u00e9cembre 2008","format":false,"excerpt":"Masquer la complexit\u00e9 du sch\u00e9ma L'\u00e9quipe de d\u00e9veloppement doit \u00e9crire un moteur de recherche pour une application de commerce \u00e9lectronique. Voici un extrait des tables de la base de donn\u00e9es impliqu\u00e9es dans la recherche des produits du site.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1102,"url":"https:\/\/dasini.net\/blog\/2011\/08\/29\/duplicate-key-cause-par-un-select\/","url_meta":{"origin":1256,"position":4},"title":"Duplicate key caus\u00e9 par un SELECT","author":"Olivier DASINI","date":"29 ao\u00fbt 2011","format":false,"excerpt":"Parmi les erreurs courantes en MySQL, ont trouve la fameuse ERROR 1062 (23000): Duplicate entry '2984' for key 'PRIMARY' caus\u00e9e par la tentative d'insertion d'une donn\u00e9es, d\u00e9j\u00e0 pr\u00e9sente, dans un colonne ayant une contrainte d'unicit\u00e9. Dans la m\u00eame famille il y a aussi l'erreur 1022...","rel":"","context":"Dans "Astuce"","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":788,"url":"https:\/\/dasini.net\/blog\/2009\/10\/12\/mysql-query-cache\/","url_meta":{"origin":1256,"position":5},"title":"MySQL Query cache","author":"Olivier DASINI","date":"12 octobre 2009","format":false,"excerpt":"Le cache est toujours \u00e0 jour car en cas de modification d'une table, toutes les requ\u00eates en relations avec cette table sont invalid\u00e9es. Le cache de requ\u00eates est en g\u00e9n\u00e9ral utile lorsque: Les modifications sur les tables ne sont pas tr\u00e8s fr\u00e9quentes Beaucoup de requ\u00eates de lectures identiques Utilisation de\u2026","rel":"","context":"Dans "MySQL"","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\/1256","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=1256"}],"version-history":[{"count":5,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1256\/revisions"}],"predecessor-version":[{"id":1600,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1256\/revisions\/1600"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1256"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1256"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1256"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}