Discussion :

[GueloSuperStar]

Bonjour,
je voudrais faire une requête pour récupérer tous les id_a qui ont pour id_b la valeur (1 OU 2) ET 4

--------------
| id_a | id_b |
--------------
| 1 | 1 |
| 1 | 2 |
| 1 | 4 |
| 2 | 3 |
| 2 | 2 |
| 4 | 4 |

Merci

[al1_24]

Quelque chose comme ça ?

Code :

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

1
2
3
4
5
6
7
8
9
SELECT  src.id_a
FROM    matable src
WHERE   src.id_b = 4
    AND EXISTS
        (   SELECT  NULL
            FROM    matable det
            WHERE   src.id_a = det.id_a
                AND det.id_b IN (1, 2)
        )

[CinePhil]

Il faut une auto-jointure :

Code :

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

1
2
3
4
5
SELECT idA
FROM la_table t1
INNER JOIN la_table t2 ON t2.idA = t1.idA
WHERE t1.idB = 4
	AND t2.idB IN (1, 2)

[GueloSuperStar]

Je sais qu'on peut récupérer les id_a qui ont pour id_b la valeur (1 ET 2 ET 4) avec cette requête :

Code :

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

1
2
3
4
5
SELECT id_a
FROM table
WHERE id_b IN (1,2,4) 
GROUP BY id_a
HAVING COUNT(DISTINCT id_b) = 3;

Mais pour faire (1 OU 2) ET (4), je ne sais pas si on peut partir du même principe.

Merci

[GueloSuperStar]

al1_24 ,

Je faisais une requête similaire avant mais avec un WHERE IN et non un EXISTS avec un requête imbriquée mais dès que j'ai eu beaucoup de données, j'ai eu quelques soucis niveau performance....

Je crois, mais j'en suis pas sur, qu'un des défaut de MYSQL était les requêtes imbriquées dans des WHERE IN car sur une table de plus de 100 000/200 000, ce style de requête était d'un lenteur affreuse après avec un EXISTS ça doit être pareil ?

Merci

[aieeeuuuuu]

Bonjour,

Pour repartir du même principe, vous pouvez faire comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT id_a
FROM LaTable
WHERE id_b IN (1,2,4) 
GROUP BY id_a
HAVING COUNT(DISTINCT 
		CASE 
			WHEN id_b IN (1,2) THEN 1
			WHEN id_b = 4 THEN 2
		END
		) = 2;

Si vous n'avez pas d’unicité sur le couple (id_a, id_b), il faudra dédoublonner les requêtes proposées par CinePhil et al1_24(avec un distinct).

Si au contraire vous avez une contrainte d'unicité (et donc a priori les index qui vont avec...) la requête de al1_24 sera je pense plus performante...

[GueloSuperStar]

CinePhil,

Je suis d'accord avec l'auto jointure mais niveau performance sur une grosse table est-ce bien ?
Car justement je ne voulais pas partir sur ce genre de requête que ce soit avec une requête imbriqué ou avec auto jointure car je crois que son exécution est lente.
Dite moi si je me trompe

Je m'explique un peu plus, j'ai une table entre 500 000 et 1 000 000 de lignes donc si je dois faire un requête de ce style :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT idA
FROM la_table t1
INNER JOIN la_table t2 ON t2.idA = t1.idA
INNER JOIN la_table t3 ON t3.idA = t1.idA
INNER JOIN la_table t4 ON t4.idA = t1.idA
WHERE t1.idB = 4
AND t2.idB IN (1, 2)
AND t3.idB IN (100,200)
AND t4.idB = 300

Chaque jointure me retourne plus de 300000 lignes comment va ce comporter un telle requête?

Merci.

[al1_24]

Je ne connais pas l'optimiseur de MySQL mais je crains qu'il ne construise le sous-ensemble du IN avant d'exécuter le reste de la requête.
Théoriquement, si tu as un index sur (id_a, id_b), toute la recherche devrait se faire sur l'index...

[CinePhil]

Tu as essayé les deux solutions qu'on t'a données ?

La solution de al1_24 avec le EXISTS est peut-être un poil plus rapide avec un grand nombre de données.

[GueloSuperStar]

Oui j'étais passé par ce type de requête imbriqué et auto- jointure et ce n'était pas concluant.
Mais pour pas dire n'importe quoi je vais les tester tout de suite et reviens vers vous.

Pour tout vous dire, pour faire obtenir le résultat voulu, je passe actuellement par des tables temporaires ...
ET la pareil niveau performance ce n'est pas trop top

Merci pour votre aide.

[skuatamad]

Et la solution d'aieeeuuuuu un peu au dessus ?
http://www.developpez.net/forums/d13...p/#post7155464

[fsmrel]

GueloSuperStar,


Avez-vous un index sur id_b ? Que donne un EXPLAIN pour les différentes requêtes présentées ?

[GueloSuperStar]

Une requête avec et sans EXISTS

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
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,15 sec)
 
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,29 sec)

Ou j'ai peut être mal écrit la requête avec le EXIST ?

DU coup si j'utilise cette requête je voudrais plus faire quelque chose 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 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')
)
AND EXISTS(
SELECT NULL 
FROM `table3` as sc2 
WHERE sc2.r_id = rr.rm_r_id AND sc2.rm_id IN ('2885','2884','2883','2882','2881','2880','2879','2878','2877','2876','2875')
);

En gros id = (1 OU 2) ET (4 OU 5) ET (10 ou 20)

Merci

[aieeeuuuuu]

Si vos colonnes sont des entiers, commencez par enlever les guillements, ça évitera au moteur de transtyper à tout va.

Ensuite, j'ai l'impression qu'il vous manque effectivement des index, mais vous ne les avez pas indiqués, et n'avez pas répondu à la question de fsmrel.

Donnez nous la structure complète de a table et de ses indexs. Une idée de la volumétrie et la répartition pourrait aussi aider...


Notez aussi que vous pouvez remplacer

Code :

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

1
2
 
IN ('2885','2884','2883','2882','2881','2880','2879','2878','2877','2876','2875')

par

Code :

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

1
2
 
BETWEEN 2875 AND 2885

Mais là encore, si la colonne n'est pas indexée, ça ne changera rien...

[GueloSuperStar]

aieeeuuuuu,

Voici pour le EXPLAIN de ma 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
 
explain 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));
+----+--------------------+-------+--------+---------------------------+---------+---------+---------------------------+--------+-------------+
| id | select_type        | table | type   | possible_keys             | key     | key_len | ref                       | rows   | Extra       |
+----+--------------------+-------+--------+---------------------------+---------+---------+---------------------------+--------+-------------+
|  1 | PRIMARY            | rs    | ref    | PRIMARY,qt_id,date_end | qt_id   | 2       | const                     | 260893 | Using where |
|  1 | PRIMARY            | rr    | ref    | rs_id                     | rs_id   | 3       | rs.rs_id   |     54 | Using where |
|  2 | DEPENDENT SUBQUERY | sc1   | eq_ref | PRIMARY,rm_id             | PRIMARY | 3       | rm_r_id |      1 | Using where |
+----+--------------------+-------+--------+---------------------------+---------+---------+---------------------------+--------+-------------+
3 rows in set (0,00 sec)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Explain 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.rs_date_end!=0
    -> AND sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11);
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type   | possible_keys             | key     | key_len | ref                     | rows | Extra       |
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | sc    | range  | PRIMARY,rm_id             | rm_id   | 3       | NULL                    |   11 | Using where |
|  1 | SIMPLE      | rr    | ref    | rs_id,rm_r_id             | rm_r_id | 3       | sc.r_id  | 1152 |             |
|  1 | SIMPLE      | rs    | eq_ref | PRIMARY,qt_id,date_end | PRIMARY | 3       | rr.rs_id |    1 | Using where |
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------------+------+-------------+
3 rows in set (0,00 sec)

Pour la table 1, il y a environ 500 000 lignes, 10 colonnes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
+-------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table1 |          0 | PRIMARY     |            1 | rs_id       | A         |      454033 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          1 | qt_id       |            1 | qt_id       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          1 | ll_id       |            1 | ll_id       | A         |          46 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          1 | pu_id       |            1 | pu_id       | A         |        2304 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          1 | ge_id       |            1 | ge_id       | A         |         641 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          1 | cc_id       |            1 | cc_id       | A         |      454033 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          1 | date_end |            1 | rs_date_end | A         |      227016 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0,00 sec)

Pour la table 2, il y a environ 21 913 471 lignes, 5 colonnes

Code :

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

1
2
3
4
5
6
7
8
9
 
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table2 |          0 | PRIMARY  |            1 | rr_id       | A         |    21913471 |     NULL | NULL   |      | BTREE      |         |               |
| table2 |          1 | rs_id    |            1 | rs_id       | A         |      405805 |     NULL | NULL   |      | BTREE      |         |               |
| table2 |          1 | rm_r_id  |            1 | rm_r_id     | A         |       19022 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0,00 sec)

Pour la table 3, il y a environ 100 000 lignes, 10 colonnes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table3 |          0 | PRIMARY   |            1 | r_id        | A         |       21530 |     NULL | NULL   |      | BTREE      |         |               |
| table3 |          1 | rm_id     |            1 | rm_id       | A         |       21530 |     NULL | NULL   |      | BTREE      |         |               |
| table3 |          1 | r_group   |            1 | r_group     | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| table3 |          1 | qm_id     |            1 | qm_id       | A         |        1266 |     NULL | NULL   |      | BTREE      |         |               |
| table3 |          1 | q_id      |            1 | q_id        | A         |         717 |     NULL | NULL   |      | BTREE      |         |               |
| table3 |          1 | q_id_next |            1 | q_id_next   | A         |         717 |     NULL | NULL   |      | BTREE      |         |               |
| table3 |          1 | q_first   |            1 | q_first     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0,00 sec)

[fsmrel]

Bonsoir,

EXPLAIN de ma requête

Dans le cas de la 1re requête (RQ1), l’optimiseur décide de commencer par exploiter la table table1 alors que dans le cas de la seconde (RQ2), il commence par exploiter la table table3. Dans les deux cas, la table2 (qui compte quand même près de 22 millions de lignes) sert de pont entre table1 et table3.

La performance de RQ1 est correcte (57 sec.), vu la volumétrie des tables (500 000 lignes pour la table table1, 22 000 000 lignes pour la table table2, 100 000 lignes pour la table table2).

La performance de RQ2 est très nettement supérieure (1,3 sec.), mais cela laisse à penser que les données étaient manifestement encore dans le cache : pour en juger objectivement, il faudrait relancer les requêtes en commençant par RQ2, puis RQ1.


Analyse de la 1re requête, RQ1

Code SQL :

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

1
2
3
4
5
6
7
8
9
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));

Explain correspondant :

+----+--------------------+-------+--------+------------------------+---------+----------+--------+-------------+
| id | select_type        | table | type   | possible_keys          | key     | ref      | rows   | Extra       |
+----+--------------------+-------+--------+------------------------+---------+----------+--------+-------------+
|  1 | PRIMARY            | rs    | ref    | PRIMARY,qt_id,date_end | qt_id   | const    | 260893 | Using where |
|  1 | PRIMARY            | rr    | ref    | rs_id                  | rs_id   | rs.rs_id |     54 | Using where |
|  2 | DEPENDENT SUBQUERY | sc1   | eq_ref | PRIMARY,rm_id          | PRIMARY | rm_r_id  |      1 | Using where |
+----+--------------------+-------+--------+------------------------+---------+----------+--------+-------------+

Il ressort que l’optimiseur décide que la 1re table accédée sera table1 (rs), à partir de l’index qt_id qui permet de filtrer les lignes pour lesquelles qt_id = 1 : il n’y a rien à redire (sauf si l’index date_end filtre mieux). La 2e table accédée sera table2, via l’index rs_id, en mode NESTED LOOP (pour chaque ligne du résultat précédent, accès direct aux lignes de la table table2 en fonction de la condition rr.rs_id = rs.rs_id) : c’est le mode classique pour résoudre la jointure. La 3e table accédée sera table3, via l’index primaire, en mode NESTED LOOP à nouveau.

Il n’y a donc rien à redire, mais il serait bien de mettre à jour les statistiques du catalogue (commande ANALYSE ?) afin de voir si MySQL continue avec la même stratégie, notamment en attaquant la table table1 à partir de l’index qt_id plutôt que date_end).


Analyse de la 2e requête, RQ2

Code SQL :

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

1
2
3
4
5
6
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.rs_date_end != 0
  AND sc.rm_id IN (41,38,35,32,29,26,23,20,17,14,11);

Explain correspondant :

+----+-------------+-------+--------+------------------------+---------+----------+------+-------------+
| id | select_type | table | type   | possible_keys          | key     | ref      | rows | Extra       |
+----+-------------+-------+--------+----------------------- +---------+----------+------+-------------+
|  1 | SIMPLE      | sc    | range  | PRIMARY,rm_id          | rm_id   | NULL     |   11 | Using where |
|  1 | SIMPLE      | rr    | ref    | rs_id,rm_r_id          | rm_r_id | sc.r_id  | 1152 |             |
|  1 | SIMPLE      | rs    | eq_ref | PRIMARY,qt_id,date_end | PRIMARY | rr.rs_id |    1 | Using where |
+----+-------------+-------+--------+------------------------+---------+----- ----+------+-------------+

L’optimiseur décide cette fois-ci que la 1re table accédée sera table3 (sc), à partir de l’index rm_id qui permet de filtrer les lignes pour lesquelles est vrai le prédicat rm_id IN (41,38,35,32,29,26,23,20,17,14,11) (Type = 'range'). La 2e table accédée sera table2, via l’index rm_r_id, en mode NESTED LOOP (pour chaque ligne du résultat précédent, accès direct aux lignes de la table table2 en fonction de la condition sc1.r_id = rr.rm_r_id). La 3e table accédée sera table1, via l’index primaire, en mode NESTED LOOP là aussi.

A nouveau il n’y a rien à redire.

Incidemment, on peut relever que les index sur les colonnes r_id et rm_id de la table table3 comptent un même nombre de valeurs : puisque l’index r_id est primaire, vérifier si l’index rm_id peut être défini comme UNIQUE.


Conclusion

Les explains montrent que les index sont bien utilisés, il n’y a pas de balayages des tables (type = ALL). Les statistiques pourraient être rafraîchies. Il faudrait relancer les deux requêtes RQ1 et RQ2 en commençant par RQ2 et comparer les performances avec celles obtenues précédemment.

[GueloSuperStar]

Bonjour fsmrel,

J'ai fait les requêtes dans l'ordre que tu m'as dit et voici le résultat, il me semble identique :

Code :

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

FLUSH QUERY CACHE;FLUSH TABLES;reset query cache;

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,28 sec)

Code :

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

FLUSH QUERY CACHE;FLUSH TABLES;reset query cache;

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,56 sec)

Je comprend pas trop pourquoi il y a une tel différence entre ces 2 requêtes surtout pourquoi la requête imbriquée prend autant de temps

Je vais essayer la requête d'aieeeuuuuu (http://www.developpez.net/forums/d13...p/#post7155464) et voir si j'ai des bon résultats

Merci.

[fsmrel]

Bonjour Guelo SuperStar,

Intuitivement, le IN filtre un maximum. Que donnent les COUNT :

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));

Et

Code SQL :

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

1
2
SELECT COUNT(*) 
FROM  table3 ;

[GueloSuperStar]

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);
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
1 row in set (0,00 sec)

Code :

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

1
2
3
4
5
6
7
SELECT COUNT(*) FROM table3;
+----------+
| COUNT(*) |
+----------+
|    21530 |
+----------+
1 row in set (0,00 sec)

[fsmrel]

J'ai fait les requêtes dans l'ordre que tu m'as dit et voici le résultat, il me semble identique

Le problème est qu'il y a un FLUSH entre les deux SELECT : il faudrait l'enlever pour retrouver les conditions de la 1re mesure.