Requête avec conditions multiples (OR/AND) sur le même champ

**GueloSuperStar** · 04/03/2013, 13h11

Sans flush et c'est toujours les même résultats

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT count(1) FROM `table1` AS `rs`
    -> INNER JOIN `table2` AS `rr` ON rr.rs_id=rs.rs_id
    -> INNER JOIN `table3` AS `sc` ON rr.rm_r_id= sc.r_id
    -> WHERE (rs.qt_id=1) and rs.date_end!=0
    -> AND sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11);
+----------+
| count(1) |
+----------+
|   238711 |
+----------+
1 row in set (1,26 sec)

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT count(1) FROM `table1` AS `rs`
    -> INNER JOIN `table2` AS `rr` ON rr.rs_id=rs.rs_id
    -> WHERE (rs.qt_id=1) and rs.date_end!=0
    -> AND EXISTS(SELECT NULL FROM table3 as sc1 WHERE sc1.r_id = rr.rm_r_id AND sc1.rm_id IN (41,38,35,32,29,26,23,20,17,14,11));
+----------+
| count(1) |
+----------+
|   238711 |
+----------+
1 row in set (57,37 sec)

**fsmrel** · 04/03/2013, 13h19

Envoyé par GueloSuperStar

Code sql :

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

SELECT COUNT(*) FROM table3 WHERE rm_id IN (41,38,35,32,29,26,23,20,17,14,11);

+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+

Avec 11 lignes (lues dans l'index) au départ pour la jointure, il est normal que ça aille si vite !

Le volume initial de la boule de neige doit être un tantinet supérieur dans l'autre cas, avec les 2 variantes :

1)

Code SQL :

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

1
2
SELECT count(*) 
FROM table1 WHERE qt_id = 1

2)

Code SQL :

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

1
2
SELECT count(*) 
FROM table1 WHERE qt_id = 1  AND date_end != 0

Qu'en est-il ?

**GueloSuperStar** · 04/03/2013, 14h31

Code :

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

1
2
3
4
5
6
7
SELECT COUNT(*) FROM table1 WHERE (qt_id=1);
+----------+
| COUNT(*) |
+----------+
|   284606 |
+----------+
1 row in set (0,05 sec)

Code :

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

1
2
3
4
5
6
7
SELECT COUNT(*) FROM table1 WHERE (qt_id=1) and date_end!=0;
+----------+
| COUNT(*) |
+----------+
|   259786 |
+----------+
1 row in set (0,25 sec)

**GueloSuperStar** · 04/03/2013, 14h56

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
SELECT rs.rs_id
FROM `table1` AS `rs`
INNER JOIN `table2` AS `rr` ON rr.rs_id=rs.rs_id
INNER JOIN `table3` AS `sc` ON rr.rm_r_id= sc.r_id 
WHERE (rs.qt_id=1) and rs.date_end!=0
AND sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11,2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875)
GROUP BY rs.rs_id
HAVING COUNT(DISTINCT(
		CASE 
			WHEN sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11) THEN 1
			WHEN sc.rm_id IN (2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875) THEN 2
		END
		)) = 2;
 
+--------+
| rs_id  |
+--------+
|    195 |
|    196 |
|    197 |
|    198 |
|    199 |
|    202 |
|    203 |
|    204 |
|    208 |
|    209 |
|    210 |
|    220 |
|    221 |
|    222 |
|    223 |
|    224 |
....................
+--------+
 
237699 rows in set (2,95 sec)

Cette requête ma l'air d'être la plus rapide pour faire une sélection de type (41 OR 38 OR 35 OR 32 OR 29 OR 26 OR 23 OR 20 OR 17 OR 14 OR 11) AND (2885 OR 2884 OR 2883 OR 2882 OR 2881 OR 2880 OR 2879 OR 2878 OR 2877 OR 2876 OR 2875).
Je ne suis pas sur que c'est la meilleur façon de faire mais au niveau perf, elle répond pas mal.
Il faut encore que je vérifie l’exactitude du résultat et voir comment je pourrais le mettre en place si c'est la bonne solution.

Par contre avec cette manière de faire, je ne peux pas faire un count() donc il faut que je passe par une table temporaire ?

Merci

**Fred_34** · 04/03/2013, 15h36

Ca ne change probablement pas grand chose, mais tu dois pouvoir faire comme ça :

Code :

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

1
2
3
4
5
HAVING COUNT(DISTINCT(
		CASE 
			WHEN sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11) THEN 1 ELSE 2
		END
		)) = 2;

Ou bien :

Code :

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

HAVING COUNT(DISTINCT(IF (sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11), 1,2))) = 2;

**GueloSuperStar** · 04/03/2013, 15h59

Fred_34,

Avec t'es modifications, je peux pas faire un requête comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT rs.rs_id
FROM `table1` AS `rs`
INNER JOIN `table2` AS `rr` ON rr.rs_id=rs.rs_id
INNER JOIN `table3` AS `sc` ON rr.rm_r_id= sc.r_id 
WHERE (rs.qt_id=1) AND rs.date_end!=0
AND sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11,2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875,1,2,3,4,5,6,7,8,9)
GROUP BY rs.rs_id
HAVING COUNT(DISTINCT(
		CASE 
			WHEN sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11) THEN 1
			WHEN sc.rm_id IN (2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875) THEN 2
			WHEN sc.rm_id IN (1,2,3,4,5,6,7,8,9) THEN 3
		END
		)) = 3;

Pour récupérer les id qui correspondent à :

(41 OR 38 OR 35 OR 32 OR 29 OR 26 OR 23 OR 20 OR 17 OR 14 OR 11)

AND (2885 OR 2884 OR 2883 OR 2882 OR 2881 OR 2880 OR 2879 OR 2878 OR 2877 OR 2876 OR 2875)

AND (1 OR 2 OR 3 OR 4 OR 5 OR 6 OR 7 OR 8 OR 9)

Sachant que ma requête est dynamique il peut y avoir 1 ou n "WHEN"

Merci

**fsmrel** · 04/03/2013, 16h06

1,3 sec. Contre 58 sec. : les choses s’expliquent.

Dans un cas, après avoir exploité l’index rm_id (table3), MySQL plonge 11 fois à la « pêche à la ligne » dans table2 pour récupérer via l‘index rs_id les lignes (en fait les valeurs de rs_id) pour les lesquelles on vérifie rr.rs_id = rs.rs_id, ce qui est réduit : de l’ordre de 1100 lignes par référence à l’explain.

Dans l’autre cas de figure, en partant de table1, MySQL plonge environ 260 000 fois dans table2 : ceci explique cela.

Concernant la requête de aieeeuuuuu, là encore MySQL plonge un nombre probablement très réduit de fois : que donne la requête :

Code SQL :

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

1
2
3
SELECT COUNT(*) 
FROM  table3 
WHERE rm_id IN (41,38,35,32,29,26,23,20,17,14,11, 2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875));

**GueloSuperStar** · 04/03/2013, 17h51

Merci pour les explictations fsmrel, je comprendre un peu mieux le fonctionnement du moteur MYSQL

Je savais que les requêtes imbriquées étaient lente, car je pensais que pour chaque ligne retourné par la première requête le moteur exécutait le requête imbriquée.

Code :

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

1
2
3
4
5
6
7
SELECT COUNT(*) FROM  table3 WHERE rm_id IN (41,38,35,32,29,26,23,20,17,14,11, 2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875);
+----------+
| COUNT(*) |
+----------+
|       77 |
+----------+
1 row in set (0,00 sec)

**GueloSuperStar** · 04/03/2013, 18h20

Je pense que cette requête est la meilleur pour ce que je veux faire :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT rs.rs_id
FROM `table1` AS `rs`
INNER JOIN `table2` AS `rr` ON rr.rs_id=rs.rs_id
INNER JOIN `table3` AS `sc` ON rr.rm_r_id= sc.r_id 
WHERE (rs.qt_id=1) AND rs.date_end!=0
AND sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11,2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875)
GROUP BY rs.rs_id
HAVING COUNT(DISTINCT(
		CASE 
			WHEN sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11) THEN 1
			WHEN sc.rm_id IN (2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875) THEN 2
		END
		)) = 2;
237699 rows IN SET (2,95 sec)

Car pour l'auto-jointure je vois pas comment je peux faire car j'ai forcement cette jointure :

Code :

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

1
2
SELECT rr.rs_id FROM `table2` AS `rr` 
INNER JOIN `table3` AS `sc` ON rr.rm_r_id= sc.r_id

Et que ma condition ce porte sur rm_id qui se trouve dans la table3 et que je veux ressortir les rs_id qui se trouvent dans la table 2

Par contre l'inconvénient de cette technique est qu'il faut que je passe par un table temporaire pour faire un count()

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TEMPORARY TABLE `temp` (`rs_id` int unsigned NOT NULL,UNIQUE KEY `rs_id` (`rs_id`)) ENGINE=MEMORY
SELECT rr.rs_id FROM `table2` AS `rr` 
INNER JOIN `table3` AS `sc` ON rr.rm_r_id= sc.r_id 
WHERE sc.rm_id IN (14,11,2885,2884,8416,8414,63993,2870) 
GROUP BY rr.rs_id 
HAVING COUNT(DISTINCT(
	CASE WHEN sc.rm_id IN (14,11) THEN 1
		WHEN sc.rm_id IN (2885,2884) THEN 2 
		WHEN sc.rm_id IN (8416,8414,63993,2870) THEN 3 
	END)) = 3;

Ou peut être je me trompe sur la façon de faire et qu'il y a une autre méthode....

**fsmrel** · 04/03/2013, 18h57

Quand le IN fait l’objet d’une liste de valeurs pour laquelle le résultat comporte 11 lignes, la durée de la requête est de 1,3 sec.

Quand le IN fait l’objet d’une liste de valeurs un peu supérieure en nombre, on passe à un résultat de 77 lignes et la durée de la requête est de 2,95 sec.

Vous parlez d’une liste de N WHEN : la durée de la requête sera fonction du nombre M de valeurs dont le IN fera par conséquent l'objet. Si vous avez un ordre de grandeur pour la valeur maximale de M, chronométrez la durée en ce sens pour avoir une connaissance objective de la performance de la requête en régime de croisière.

**Fred_34** · 04/03/2013, 19h47

Pas la peine de créer explicitement une table temporaire, il te suffit de faire comme ça :

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
SELECT COUNT(*) FROM (
	SELECT rs.rs_id
	FROM `table1` AS `rs`
	INNER JOIN `table2` AS `rr` ON rr.rs_id=rs.rs_id
	INNER JOIN `table3` AS `sc` ON rr.rm_r_id= sc.r_id 
	WHERE (rs.qt_id=1) AND rs.date_end!=0
	AND sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11,2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875)
	GROUP BY rs.rs_id
	HAVING COUNT(DISTINCT(
			CASE 
				WHEN sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11) THEN 1
				WHEN sc.rm_id IN (2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875) THEN 2
			END
			)) = 2
) tmp;

**GueloSuperStar** · 05/03/2013, 10h42

Fred_34, oui effectivement on peut faire dans le SELECT

fsmrel merci pour les explications, je vais faire les tests nécessaire pour voir les performances.

Requête avec conditions multiples (OR/AND) sur le même champ

Langage SQL

Discussions similaires

Partager

Partager