[MySQL-5.5] Performance sur l'union de sous-requêtes

**flashguitou** · 28/06/2013, 11h48

Bonjour à tous,

Je souhaite assembler le résultat de plusieurs sous-requêtes, cela fonctionne mais dans des temps par très convenables.

Voici la requête :

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
 SELECT DISTINCT( tr.id_link ) AS id
FROM   table_liaison_communication AS tr
WHERE  tr.id IN (SELECT DISTINCT( tr.id )
                 FROM   table_liaison_communication AS tr
                 WHERE  tr.id IN (SELECT DISTINCT( tr.id )
                                  FROM   table_liaison_communication AS tr
                                         LEFT JOIN table_enr AS trr
                                                ON tr.id_link = trr.id
                                         LEFT JOIN table_data AS t1
                                                ON tr.id_link = t1.id_link
                                                   AND tr.key_name = t1.key_name
                                         LEFT JOIN table_index_nom AS t2
                                                ON tr.id_link = t2.id_link
                                  WHERE  t1.key_name != ''
                                         AND t1.key_name LIKE '%email%'
                                         AND t1.val LIKE '%e%'
                                         AND t2.val LIKE 'a%t%r'
                                         AND trr.profil = '1 '
                                         AND trr.acces = '1'
                                         AND trr.etat = '1'
                                         AND trr.id_link = '0')
                 UNION
                 (SELECT DISTINCT( tr.id )
                  FROM   table_liaison_communication AS tr
                         LEFT JOIN table_enr AS trr
                                ON tr.id_link = trr.id
                         LEFT JOIN table_data AS t1
                                ON tr.id_link = t1.id_link
                                   AND tr.key_name = t1.key_name
                         LEFT JOIN table_index_nom AS t2
                                ON tr.id_link = t2.id_link
                  WHERE  t1.key_name != ''
                         AND t1.key_name LIKE '%email%'
                         AND t1.val LIKE '%g%'
                         AND t2.val LIKE 'r%e%'
                         AND trr.profil = '1 '
                         AND trr.acces = '1'
                         AND trr.etat = '1'
                         AND trr.id_link = '0'))
UNION
(SELECT DISTINCT( tr.id )
 FROM   table_liaison_communication AS tr
        LEFT JOIN table_enr AS trr
               ON tr.id_link = trr.id
        LEFT JOIN table_data AS t1
               ON tr.id_link = t1.id_link
                  AND tr.key_name = t1.key_name
        LEFT JOIN table_index_nom AS t2
               ON tr.id_link = t2.id_link
 WHERE  t1.key_name != ''
        AND t1.key_name LIKE '%email%'
        AND t1.val LIKE '%e%'
        AND t2.val LIKE 'e%m%'
        AND trr.profil = '1 '
        AND trr.acces = '1'
        AND trr.etat = '1'
        AND trr.id_link = '0')

Elle est constituée de 3 sous-requêtes que j'essaye d'unir en un seul résultat

select ... from (
(sous-r 1)
UNION
(sous-r 2)
)
UNION
(sous-r 3)

Indépendamment, les sous requêtes s’exécutent très rapidement, exemple pour la 1ere sous-requête : Traitement en 0.0003 sec

Alors pourquoi ma requête Finale met plus de 2 seconde à me sortir le résultat ???

En passant par l'utilisation de tables temporaires, cela va plus vite, mais me demande de scripter la logique combinatoire des données en php exemple :

A1, A2, A3 sont des tables temporaires des résultats de mes 3 sous-requêtes que je réuni au final par une dernière requête :

Code :

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

1
2
3
4
5
6
7
8
 
mysql_query("CREATE TEMPORARY TABLE A4 SELECT (id) from A1 UNION SELECT (id) from A2");
 
mysql_query("CREATE TEMPORARY TABLE A5 SELECT (id) from A4 UNION SELECT (id) from A3");
 
mysql_query("SELECT DISTINCT(tr.id_link) 
FROM table_liaison_communication as tr, A5 
WHERE tr.id = A5.id");

et j'arrive à un résultat en moins de 300 ms, beaucoup plus honorable !

Merci d'avance pour vos conseils,

Flashguitou

PS : les tables utilisées :

La table table_enr :

la table table_data :

la table table_liaison_communication :

**punkoff** · 28/06/2013, 11h55

bonjour,

je ne comprend pas bien linteret de faire autant de sous-requete.

L'opérateur union ne prend pas les doublons des autres requêtes, du coup :

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
SELECT DISTINCT( tr.id )
FROM   table_liaison_communication AS tr
	 LEFT JOIN table_enr AS trr
			ON tr.id_link = trr.id
	 LEFT JOIN table_data AS t1
			ON tr.id_link = t1.id_link
			   AND tr.key_name = t1.key_name
	 LEFT JOIN table_index_nom AS t2
			ON tr.id_link = t2.id_link
WHERE  t1.key_name != ''
	 AND t1.key_name LIKE '%email%'
	 AND t1.val LIKE '%e%'
	 AND t2.val LIKE 'a%t%r'
	 AND trr.profil = '1 '
	 AND trr.acces = '1'
	 AND trr.etat = '1'
	 AND trr.id_link = '0'
UNION
SELECT DISTINCT( tr.id )
FROM   table_liaison_communication AS tr
	 LEFT JOIN table_enr AS trr
			ON tr.id_link = trr.id
	 LEFT JOIN table_data AS t1
			ON tr.id_link = t1.id_link
			   AND tr.key_name = t1.key_name
	 LEFT JOIN table_index_nom AS t2
			ON tr.id_link = t2.id_link
WHERE  t1.key_name != ''
	 AND t1.key_name LIKE '%email%'
	 AND t1.val LIKE '%g%'
	 AND t2.val LIKE 'r%e%'
	 AND trr.profil = '1 '
	 AND trr.acces = '1'
	 AND trr.etat = '1'
	 AND trr.id_link = '0'
UNION
SELECT DISTINCT( tr.id )
 FROM   table_liaison_communication AS tr
        LEFT JOIN table_enr AS trr
               ON tr.id_link = trr.id
        LEFT JOIN table_data AS t1
               ON tr.id_link = t1.id_link
                  AND tr.key_name = t1.key_name
        LEFT JOIN table_index_nom AS t2
               ON tr.id_link = t2.id_link
 WHERE  t1.key_name != ''
        AND t1.key_name LIKE '%email%'
        AND t1.val LIKE '%e%'
        AND t2.val LIKE 'e%m%'
        AND trr.profil = '1 '
        AND trr.acces = '1'
        AND trr.etat = '1'
        AND trr.id_link = '0'

Ensuite, vous ne faites pas de jointures externe mais des jointures interne au vu des conditions de jointure.

Et enfin dégagez les distinct si vous n'en avez pas besoin.

**flashguitou** · 28/06/2013, 14h39

Merci pour ces précieux conseils.

Par rapport à votre proposition de requête si j’exécute :

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
SELECT  tr.id 
FROM   table_liaison_communication AS tr
     LEFT JOIN table_enr AS trr
            ON tr.id_link = trr.id
     LEFT JOIN table_data AS t1
            ON tr.id_link = t1.id_link
               AND tr.key_name = t1.key_name
     LEFT JOIN table_index_nom AS t2
            ON tr.id_link = t2.id_link
WHERE  t1.key_name != ''
     AND t1.key_name LIKE '%email%'
     AND t1.val LIKE '%e%'
     AND t2.val LIKE 'a%t%r'
     AND trr.profil = '1 '
     AND trr.acces = '1'
     AND trr.etat = '1'
     AND trr.id_link = '0'
UNION
SELECT tr.id 
FROM   table_liaison_communication AS tr
     LEFT JOIN table_enr AS trr
            ON tr.id_link = trr.id
     LEFT JOIN table_data AS t1
            ON tr.id_link = t1.id_link
               AND tr.key_name = t1.key_name
     LEFT JOIN table_index_nom AS t2
            ON tr.id_link = t2.id_link
WHERE  t1.key_name != ''
     AND t1.key_name LIKE '%email%'
     AND t1.val LIKE '%g%'
     AND t2.val LIKE 'r%e%'
     AND trr.profil = '1 '
     AND trr.acces = '1'
     AND trr.etat = '1'
     AND trr.id_link = '0'
UNION
SELECT tr.id 
 FROM   table_liaison_communication AS tr
        LEFT JOIN table_enr AS trr
               ON tr.id_link = trr.id
        LEFT JOIN table_data AS t1
               ON tr.id_link = t1.id_link
                  AND tr.key_name = t1.key_name
        LEFT JOIN table_index_nom AS t2
               ON tr.id_link = t2.id_link
 WHERE  t1.key_name != ''
        AND t1.key_name LIKE '%email%'
        AND t1.val LIKE '%e%'
        AND t2.val LIKE 'e%m%'
        AND trr.profil = '1 '
        AND trr.acces = '1'
        AND trr.etat = '1'
        AND trr.id_link = '0'

cela me donne un résultat en ( 207 total, Traitement en 0.0890 sec)

Mais dès que je met :

Code :

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

SELECT DISTINCT id_link FROM table_liaison_communication WHERE id IN(...votre requête...)

Pour obtenir mon resultat final cela met : ( 176 total, Traitement en 1.0806 sec)
Résultat correct mais qui met en fait bien plus de 3 secondes à remonter au final(plus que 1.0806 sec) pourtant cela me semble être une requête toute bête ???

En gros est ce normal de passer de 0,0890 seconde à + 1 seconde ?

Soit plus 10X plus de temps ?

Merci de votre aide !

Bonne journée,

**punkoff** · 28/06/2013, 18h38

Envoyé par flashguitou

En gros est ce normal de passer de 0,0890 seconde à + 1 seconde ?

Soit plus 10X plus de temps ?

Avec MySql et des sous-requetes ca ne me choque pas.
L'optimiseur est à la ramasse complet dès que l'on sort des simples jointures.

Regardez du côte de PostGreSql voir Sql serveur (l'édition gratuite).

Sinon essayez ca (j'avais pas vu le cou pdu id_link) :

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
SELECT DISTINCT( tr.id_link )
FROM   table_liaison_communication AS tr
	 LEFT JOIN table_enr AS trr
			ON tr.id_link = trr.id
	 LEFT JOIN table_data AS t1
			ON tr.id_link = t1.id_link
			   AND tr.key_name = t1.key_name
	 LEFT JOIN table_index_nom AS t2
			ON tr.id_link = t2.id_link
WHERE  t1.key_name != ''
	 AND t1.key_name LIKE '%email%'
	 AND t1.val LIKE '%e%'
	 AND t2.val LIKE 'a%t%r'
	 AND trr.profil = '1 '
	 AND trr.acces = '1'
	 AND trr.etat = '1'
	 AND trr.id_link = '0'
UNION
SELECT DISTINCT( tr.id_link )
FROM   table_liaison_communication AS tr
	 LEFT JOIN table_enr AS trr
			ON tr.id_link = trr.id
	 LEFT JOIN table_data AS t1
			ON tr.id_link = t1.id_link
			   AND tr.key_name = t1.key_name
	 LEFT JOIN table_index_nom AS t2
			ON tr.id_link = t2.id_link
WHERE  t1.key_name != ''
	 AND t1.key_name LIKE '%email%'
	 AND t1.val LIKE '%g%'
	 AND t2.val LIKE 'r%e%'
	 AND trr.profil = '1 '
	 AND trr.acces = '1'
	 AND trr.etat = '1'
	 AND trr.id_link = '0'
UNION
SELECT DISTINCT( tr.id_link )
 FROM   table_liaison_communication AS tr
        LEFT JOIN table_enr AS trr
               ON tr.id_link = trr.id
        LEFT JOIN table_data AS t1
               ON tr.id_link = t1.id_link
                  AND tr.key_name = t1.key_name
        LEFT JOIN table_index_nom AS t2
               ON tr.id_link = t2.id_link
 WHERE  t1.key_name != ''
        AND t1.key_name LIKE '%email%'
        AND t1.val LIKE '%e%'
        AND t2.val LIKE 'e%m%'
        AND trr.profil = '1 '
        AND trr.acces = '1'
        AND trr.etat = '1'
        AND trr.id_link = '0'

**flashguitou** · 28/06/2013, 23h07

Ha la effectivement c'est instantané !

( 176 total, Traitement en 0.0002 sec)

Donc va falloir revoir un peu le montage logique de mes requêtes merci beaucoup !

Après effectivement je vais suivre vos conseils en allant tester des SGDB surement mieux foutu pour les sous-requêtes à gros volume.

Merci !

**punkoff** · 28/06/2013, 23h09

vos volume ne sont pas gros.

C'est juste MySql qui gere mal les choses, apres oui problème de logique dans la requete qui n'aide pas vraiment l'optimiseur...

**tse_jc** · 30/06/2013, 09h07

Bonjour,

Heureusement que les volumes ne sont pas gros car

AND t1.key_name LIKE '%email%'
AND t1.val LIKE '%e%'

désactive l'utilisation d'index(s) et oblige au full scan de(s) table(s) concernée(s).

++

**flashguitou** · 30/06/2013, 15h16

J'ai mis mes ces 2 colonnes :
key_name : FULLTEXT
val : FULLTEXT

Pour une utilisation : ...AND t1.key_name LIKE '%email%'
AND t1.val LIKE '%e%'...

c'est ce qu'il faut ?

Bon week !

**tse_jc** · 30/06/2013, 19h15

Bonjour,

c'est ce qu'il faut ?

Désolé mais ma boule de cristal est en panne, ne connaissant pas vos contraintes métier et votre environnement/contexte d'utilisation, il m'est impossible de vous proposer une alternative viable.

D'autre part, je me permet de vous rappeler qu'en MySQL il ne suffit pas de définir un index en fulltext pour faire du fulltext mais il vous faut utiliser une expression MATCH... AGAINST pour ce faire et non un opérateur logique comme LIKE.
De plus dans la configuration par défaut de MySQL pour une recherche en mode fulltext, le pattern de recherche est défini à 4 caractères au minimum.

Cordialement,

Jc.

**flashguitou** · 30/06/2013, 20h39

Merci pour ces infos !

[MySQL-5.5] Performance sur l'union de sous-requêtes

Requêtes MySQL

Discussions similaires

Partager

Partager