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
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
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) )
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)
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 :
Mais pour faire (1 OU 2) ET (4), je ne sais pas si on peut partir du même principe.
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;
Merci
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
Bonjour,
Pour repartir du même principe, vous pouvez faire comme ça :
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).
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 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...
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.
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...
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.
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.
Et la solution d'aieeeuuuuu un peu au dessus ?
http://www.developpez.net/forums/d13...p/#post7155464
GueloSuperStar,
Avez-vous un index sur id_b ? Que donne un EXPLAIN pour les différentes requêtes présentées ?
Une requête avec et sans EXISTS
Ou j'ai peut être mal écrit la requête avec le EXIST ?
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)
DU coup si j'utilise cette requête je voudrais plus faire quelque chose comme ça
En gros id = (1 OU 2) ET (4 OU 5) ET (10 ou 20)
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') );
Merci
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
par
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 IN ('2885','2884','2883','2882','2881','2880','2879','2878','2877','2876','2875')
Mais là encore, si la colonne n'est pas indexée, ça ne changera rien...
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 BETWEEN 2875 AND 2885
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)
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
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 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
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 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 +-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | 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)
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)
Bonsoir,
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 :
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.+----+--------------------+-------+--------+------------------------+---------+----------+--------+-------------+ | 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 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 :
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.+----+-------------+-------+--------+------------------------+---------+----------+------+-------------+ | 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 | +----+-------------+-------+--------+------------------------+---------+----- ----+------+-------------+
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.
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;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
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 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.
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 ;
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)
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager