tentative d'optimisation de requettes

**KINENVEU** · 06/08/2007, 23h59

bonjour, je voudrais savoir comment optimiser certaines requettes.

par exemple :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
```
SELECT t1.* FROM t1, t2 WHERE t1.id= t2.id;
```

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT t1.* FROM t1 WHERE t1.id IN (SELECT t2.id FROM t2);

Code : Sélectionner tout - Visualiser dans une fenêtre à part
```
SELECT t1.* FROM t1 INNER JOIN t2 ON t1.id= t2.id;
```

produisent-elles le meme resultat ?
quelle est la methode la plus optimale ?
ou y en a-t-il d'autres ?

si j'ai bien compris,
d'apres le manuel de reference MySQL : il faut preferer 2 a 1.
d'apres la faq ici : il faut preferer 3 a 1.

qu'en est il ?

admettons que je veuille aller plus loin,
est-ce que ces requettes sont equivalentes ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT t1.* FROM t1, t2 WHERE t1.id= t2.id AND t2.A= 'toiJeTeVeux';

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT t1.* FROM t1 WHERE t1.id IN (SELECT t2.id FROM t2 WHERE t2.A= 'toiJeTeVeux');

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT t1.* FROM t1 INNER JOIN t2 ON t1.id= t2.id WHERE t2.A= 'toiJeTeVeux';

merci par avance.

**Lawyer666** · 08/08/2007, 16h17

Oui pour moi ces requêtes sont toutes équivalentes car les jointures qui sont faites ici sont internes.

Et oui il faut préferer 3 à 1 car cela permet de bien séparer ce qui est jointure de ce qui est filtrage, même si le résultat sera identique (et même le plan d'éxecution il me semble) dans le cas de jointures internes.

Par contre je suis surpris que le manuel de référence MySql recommande le 2 plutôt que le 1, il me semblait que les sous-requêtes étaient à éviter lorsque l'on pouvait réaliser des jointures.
Aurais-tu le lien stp ?

**KINENVEU** · 09/08/2007, 07h00

merci pour tes conseils.

voici le lien sur lequel je me suis base:
http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

pourrais-tu me rafraichir la memoire,
ce que tu appelles jointure interne, c'est car t1.id = t2.id ?

justement je cherche a optimiser une requette impliquant 5 tables.
et je voudrais faire une jointure de 2 tables dependant de leurs relations avec les 3 autres ...

je ne suis peut etre pas super clair, mais voici le cas concret, ce sera plus facile de discuter.

------- 1 --------
voici la requette de base :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT t5.*, t2.* 
FROM t1, t2, t3, t4, t5 
WHERE t5.idA=t4.idA
AND t5.idA=t1.idA
AND t1.idC=t3.idC
AND t3.val='toiJeTeVeux'
AND t2.idB=t4.idB

ou idA est PRIMARY KEY de t1.
idB est PRIMARY KEY de t2.
idC est PRIMARY KEY de t3.
(idA, idB) est PRIMARY KEY de t4.
idA est PRIMARY KEY de t5. (sous-ensemble de t1)

a noter que l'ordre de grandeur en nombre de lignes est:
t1 : 10**7
t2 : 10**3
t3 : 3.10**2
t4 : 10**6
t5 : 2.10**6

la requete telle quelle prend tellement de temps, que je n'ai pas eu la patience d'attendre ...
---------- 2 -----------
1ere idee: transformer en sous-selections

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT t5.*, t2.* 
FROM t2, t5
WHERE (t5.idA, t2.idB) IN 
    (SELECT t4.idA, t4.idB 
     FROM t4 
     WHERE t4.idA IN
        (SELECT t1.idA 
         FROM t1
         WHERE t1.idC IN 
            (SELECT t3.idC 
             FROM t3 
             WHERE t3.val='toiJeTeVeux'
            )
        )
    )

la requette est toujours super longue,
et je ne sais pas si c'est mieux ... car je n'ai pas de resultat non plus
------ 3 ---------------
j'essaie avec des jointures

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT t2*, t5* FROM t5 
LEFT JOIN t1
ON t1.idA=t5.idA
RIGHT JOIN t4
ON t1.idA=t4.idA
RIGHT JOIN t2
ON t2.idB= t4.idB
WHERE t1.idC IN 
     (SELECT t3.idC FROM t3 WHERE t3.val='toiJeTeVeux')

mais je ne suis pas sur que la requette soit equivalente ...???

**nounetmasque** · 09/08/2007, 08h40

Je ne suis pas certain mais il me semble que l'ordre des jointures est important dans l'exécution des requettes SQL. En effet, il me semble plus judicieux de commencer une requette par le filtre le plus discriminant.
Cette requette est exactement la même que la tienne avec l'ordre modifié :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT t5.*, t2.* 
FROM t1, t2, t3, t4, t5 
WHERE t3.val='toiJeTeVeux'
AND t3.idC=t1.idC
AND t1.idA=t5.idA
AND t5.idA=t4.idA
AND t4.idB=t2.idB

De plus as-tu mis des index pertinants sur tes 5 tables ?

**Antoun** · 09/08/2007, 10h42

Envoyé par KINENVEU

bonjour, je voudrais savoir comment optimiser certaines requettes.

par exemple :
(première série)

produisent-elles le meme resultat ?

La 1 et la 3 produisent le même résultat. Par contre, si une t1.id correspond à 5 lignes dans t2, ces deux requêtes te donneront 5 lignes, tandis que la 2 ne te donnera qu'une ligne.

Envoyé par KINENVEU

quelle est la methode la plus optimale ?
ou y en a-t-il d'autres ?

si j'ai bien compris,
d'apres le manuel de reference MySQL : il faut preferer 2 a 1.

Je pense que tu as mal compris. Le IN (sous-requête) est très mal optimisé. La 2) sera donc probablement bcp plus lente que les deux autres.

Envoyé par KINENVEU

d'apres la faq ici : il faut preferer 3 a 1.

oui, mais essentiellement pour des raisons de lisibilité, et pas vraiment de performance.

Envoyé par KINENVEU

admettons que je veuille aller plus loin,
est-ce que ces requettes sont equivalentes ?
(seconde série)

Même réponse, pour les mêmes raisons. 1 et 3 sont équivalentes. 2 peut donner moins de lignes. 3 est plus lisible et plus logique en termes d'écriture.

**Antoun** · 09/08/2007, 11h04

Envoyé par KINENVEU

merci pour tes conseils.

voici le lien sur lequel je me suis base:
http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

Tu as effectivement mal compris :

- cette partie te présente une série d'équivalences, sans trop se mouiller sur la question de savoir si la sous-requête sera plus performante ou pas (généralement, elle est ou équivalente ou bcp plus mauvaise).

- le cas présenté ou IN est équivalent à une jointure est un DISTINCT, ce qui élimine les questions de différence de nb de lignes entre IN et jointure.

Envoyé par KINENVEU

pourrais-tu me rafraichir la memoire,
ce que tu appelles jointure interne, c'est car t1.id = t2.id ?

Rien à voir. La jointure est interne parce que tu n'as précisé qu'elle devait être externe.
Supposons que tu aies dans t1 une ID 6 qui n'existe pas dans t2 :

- avec t1 INNER JOIN t2 ON t1.id = t2.id (ou FROM t1, t2 WHERE t1.id = t2.id), la ligne avec t1.id = 6 sera éliminée

- avec t1 LEFT OUTER JOIN t2 ON t1.id = t2.id, cette même ligne sera conservée : LEFT OUTER définit que la table à gauche (donc t1) est obligatoire

- avec t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id, la ligne où t1.id = 6 sera éliminée aussi, puisque c'est t2 qui est obligatoire. S'il existe une t2.id qui n'est pas dans t1, cela se traduira par une ligne de NULL.

- avec certains autres SGBD, tu pourrais aussi faire t1 FULL OUTER JOIN t2 ON t1.id = t2.id, qui combine LEFT et RIGHT (autrement dit, t1 et t2 sont toutes deux obligatoires).

**pop_up** · 09/08/2007, 11h28

Juste un petit complément si ça peut aider :
L'inconvénient des sous requetes c'est que l'index(si il existe) est declenché uniquement sur la requete principale et pas la sous requete.

En revanche avec un INNER JOIN tous les indexs seronts utilisés.

**jota5450** · 09/08/2007, 12h01

slt ...

L'inconvénient des sous requetes c'est que l'index(si il existe) est declenché uniquement sur la requete principale et pas la sous requete.

je suis pas sur de t´avoir bien compris....

l´index est pris meme dans les sous requetes... ca depant de quelques conditions..

un exemple simple...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE `table_1` (
  `id_1` int(10) unsigned NOT NULL auto_increment,
  `nom` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`id_1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `table_1` (id_1,nom) VALUES 
 (1,'a'),
 (2,'b'),
 (3,'c'),
 (4,'d'),
 (5,'e'),
 (6,'f');

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE `table_2` (
  `id_2` int(10) unsigned NOT NULL auto_increment,
  `id_table_1` int(10) unsigned NOT NULL default '0',
  `nom` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`id_2`),
  KEY `mon_Index` (`id_table_1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `table_2` (id_2,id_table_1,nom) VALUES 
 (1,2,'aaa'),
 (2,2,'bbb'),
 (3,3,'ccc');

requete "normal"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
mysql> explain(SELECT * FROM test.table_1 t where id_1 in (SELECT id_table_1 FRO
M test.table_2 t2  ));
+----+--------------------+-------+----------------+---------------+-----------+
---------+------+------+-------------+
| id | select_type        | table | type           | possible_keys | key       |
 key_len | ref  | rows | Extra       |
+----+--------------------+-------+----------------+---------------+-----------+
---------+------+------+-------------+
| 1  | PRIMARY            | t     | ALL            |               |           |
         |      | 6    | Using where |
| 2  | DEPENDENT SUBQUERY | t2    | index_subquery | mon_Index     | mon_Index |
 4       | func | 1    | Using index |
+----+--------------------+-------+----------------+---------------+-----------+
---------+------+------+-------------+
2 rows in set (0.00 sec)

requete sans index..

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
mysql> explain(SELECT * FROM test.table_1 t where id_1 in (SELECT id_table_1 FRO
M test.table_2 t2 ignore index(Mon_index) ));
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref
 | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+-------------+
| 1  | PRIMARY            | t     | ALL  |               |      |         |
 | 6    | Using where |
| 2  | DEPENDENT SUBQUERY | t2    | ALL  |               |      |         |
 | 3    | Using where |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+-------------+
2 rows in set (0.00 sec)

**pop_up** · 09/08/2007, 12h12

a la base j'avais ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT  DISTINCT id_adresse, id_pers
FROM    TABLE1
WHERE  id_ville IN (SELECT id_ville
                          FROM TABLE2
                          WHERE col6='1' AND col7='titi');

avec un index sur table1.id_ville et un autre sur table2(col6,col7).
Le probleme c'etait qu'en faisant un explain ça ne declenchai que le premier index de la table 1

en revanche avec ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT  DISTINCT id_adresse, id_pers
	FROM    TABLE1 INNER JOIN TABLE2 ON (TABLE1.id_ville=TABLE2.id_ville
				AND TABLE2.col6='1' AND TABLE2.col7='titi');

et en modifiant l'index pour le mettre sur(id_ville,col6,col7) pour la table2, les deux indexs sont pris en compte.

J'aurai fait une erreur quelque part ?

**jota5450** · 09/08/2007, 13h05

slt...

ton index sur la table2 est bien (col6,col7), et non (colx,col6,col7)?

t´avais pas d´autres clauses dans le where?

tu peus montrer le show create des 2 tables?

**pop_up** · 09/08/2007, 13h32

en fait j'ai plus ces tables puisque j'ai altéré mon modele et que j'ai tout refait.

Donc les indexs se declenchent bien dans les sous requetes alors.

**KINENVEU** · 09/08/2007, 23h25

Envoyé par nounetmasque

Je ne suis pas certain mais il me semble que l'ordre des jointures est important dans l'exécution des requettes SQL. En effet, il me semble plus judicieux de commencer une requette par le filtre le plus discriminant.
Cette requette est exactement la même que la tienne avec l'ordre modifié :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT t5.*, t2.* 
FROM t1, t2, t3, t4, t5 
WHERE t3.val='toiJeTeVeux'
AND t3.idC=t1.idC
AND t1.idA=t5.idA
AND t5.idA=t4.idA
AND t4.idB=t2.idB

merci, c'est bon a savoir.

Envoyé par nounetmasque

De plus as-tu mis des index pertinants sur tes 5 tables ?

oui, c'est le cas :-)

**KINENVEU** · 09/08/2007, 23h34

Envoyé par Antoun

Tu as effectivement mal compris :

on pourrait ne pas le croire, mais je suis ravi que tu me reprennes ;-)

Envoyé par Antoun

Supposons que tu aies dans t1 une ID 6 qui n'existe pas dans t2 :

- avec t1 INNER JOIN t2 ON t1.id = t2.id (ou FROM t1, t2 WHERE t1.id = t2.id), la ligne avec t1.id = 6 sera éliminée

- avec t1 LEFT OUTER JOIN t2 ON t1.id = t2.id, cette même ligne sera conservée : LEFT OUTER définit que la table à gauche (donc t1) est obligatoire

- avec t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id, la ligne où t1.id = 6 sera éliminée aussi, puisque c'est t2 qui est obligatoire. S'il existe une t2.id qui n'est pas dans t1, cela se traduira par une ligne de NULL.

- avec certains autres SGBD, tu pourrais aussi faire t1 FULL OUTER JOIN t2 ON t1.id = t2.id, qui combine LEFT et RIGHT (autrement dit, t1 et t2 sont toutes deux obligatoires).

vraiment merci pour ces precisions, ca va m'etre tres utile.

**KINENVEU** · 09/08/2007, 23h52

J'aimerai essayer de resumer tous ca :

Pour optimiser des requettes Mysql,

Il faut preferer les jointures(JOIN) aux sous-selections (IN).
Suivant le resultat voulu, on choisit le type de jointures(INNER, OUTER, LEFT, RIGHT)
L'ordre des jointures pourrait influer sur l'efficacite d'une requette.
Mettre les filtres(WHERE) les plus discriminants en 1er.
Il faut avoir des index pertinants.

j'espere que cette fois j'ai bien compris ;-)

merci a tous de votre aide.