Jointure vs sous-requête

mai 15, 2012

MySQL est connu pour ne pas être très performant avec les sous-requêtes. Ce n’est pas faux, et d’ailleurs c’est encore le cas avec MySQL 5.5. Le contournement consiste en général à réécrire la requête, certaines sous-requêtes pouvant être aisément réécrite en jointure.

C’est le cas de
SELECT a FROM T1 WHERE col IN (SELECT col FROM T2…)  qui se transforme en
SELECT distinct a FROM T1 INNER JOIN T2 ON TI.col=T2.col WHERE …
Avec un exemple concret, il est possible de faire les constatations suivantes.

Serveur : MySQL: 5.5.22 Community Server (GPL)

 

Requête 1 :

 

 

Requête 2 (requête 1 réécrite avec une jointure) :

 

P.S. Le critère de l’auto-jointure étant sur toutes les parties de l’index unique (la clé primaire), il n’ est donc pas nécessaire de déboublonner avec DISTINCT (ou GROUP BY).

 

 

Après benchs, l’écart entre ses 2 requêtes est d’environ 7 %. Dans la vraie vie, l’écart peut aller de quelques secondes à une requête qui ne rend jamais la main…

 

La lecture du plan d’exécution (QEP) donné par la commande EXPLAIN, n’explique pas de manière flagrante la différence de performance.

Requête 1 :

table: customer

type: unique_subquery

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: func

rows: 1

Extra: Using where

 

Requête 2 :

id: 1

select_type: SIMPLE

table: c1

type: index

possible_keys: PRIMARY

key: idx_lname

key_len: 50

ref: NULL

rows: 700021

Extra: Using index

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: c2

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: customer.c1.id_cutomer

rows: 1

Extra: Using where

 

 

Qu’en est il de la version 5.6 de MySQL ?

Cette dernière mouture apporte une ribambelle d’amélioration, notamment en ce qui concerne l’optimisation des sous-requêtes…

 

Serveur : MySQL: 5.6.5-m8 MySQL Community Server (GPL)

 

Les QEPs respectifs sont

id: 1

select_type: SIMPLE

table: customer

type: index

possible_keys: PRIMARY

key: idx_lname

key_len: 50

ref: NULL

rows: 699628

Extra: Using index

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: customer

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: customer.customer.id_cutomer

rows: 1

Extra: Using where

 

et

 

id: 1

select_type: SIMPLE

table: c1

type: index

possible_keys: PRIMARY

key: idx_lname

key_len: 50

ref: NULL

rows: 699628

Extra: Using index

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: c2

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: customer.c1.id_cutomer

rows: 1

Extra: Using where

 

 

Oui, oui vous avez bien lu, ils sont bien (quasiment) identique !

En détaillant le QEP de la sous-requête (avec EXPLAIN EXTENDED), on constate que l’optimiseur à (enfin) transformé la sous-requête en jointure :

 

 

show warnings\G

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: /* select#1 */ select count(0) AS count(*) from customer.customer join customer.customer where ((customer.customer.id_cutomer = customer.customer.id_cutomer) and (customer.customer.phone like ‘06%’))

 

 

C’est une bien bonne nouvelle !

Malheureusement MySQL 5.6 n’est pas encore en GA (fin d’année à priori selon mes sources). Mais tout n’est pas perdu ! (ça s’est du suspens :D).

 

 

 

Quelques chiffres

 

Un « quick bench », avec mysqlslap

 

Requête 1 :

mysqlslap –create-schema=test -q »SELECT count(*) FROM customer WHERE id_cutomer IN (SELECT id_cutomer FROM cust WHERE phone like ‘06%’); » -i200

 

Requête 2 :

mysqlslap –create-schema=test -q »SELECT count(*) FROM customer c1 INNER JOIN customer c2 USING(id_cutomer) WHERE c2.phone like ‘06%’; » -i200

 

 

MySQL 5.5

 

Requête 1 :

Minimum number of seconds to run all queries: 0.923 seconds

 

Requête 2 :

Minimum number of seconds to run all queries: 0.844 seconds

 

 

MySQL 5.6

 

Requête 1 :

Minimum number of seconds to run all queries: 0.974 seconds

 

Requête 2 :

Minimum number of seconds to run all queries: 0.979 seconds

 

 

 

Divers

Leave a Reply