[Optim] Requête avec jointures et max [Résolu]

[GyZmoO]

Bonjour à tous.

Je me tourne vers vous, car j'ai une requête qui "mouline" bien trop longtemps..

Voici la requête en question :

Code :

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
 
EXPLAIN SELECT * FROM T_TRACE st
INNER JOIN T_MEASUREMENT_CHAIN mc ON mc.CHAIN_ID = st.CHAIN_ID
INNER JOIN T_SITE si ON si.SITE_ID = mc.SITE_ID
INNER JOIN T_ENTITY tr ON tr.XPDR_ID = st.XPDR_ID
 
WHERE
tr.XPDR_ID = 1
AND (st.TRACE_TYPE = 'myType')
AND (st.TRACE_ANSWER_TYPE = 0
      OR st.TRACE_ANSWER_TYPE = 4
      OR st.TRACE_ANSWER_TYPE = 3
      OR st.TRACE_ANSWER_TYPE = 5)
AND (si.SITE_NAME = 'Paris')
AND (mc.CHAIN_NAME = 'myChain')
AND st.TRACE_MEASURE_DATE =
 
(SELECT MAX(TRACE_MEASURE_DATE) FROM T_TRACE st
INNER JOIN T_MEASUREMENT_CHAIN mc ON mc.CHAIN_ID = st.CHAIN_ID
INNER JOIN T_SITE si ON si.SITE_ID = mc.SITE_ID
INNER JOIN T_ENTITY tr ON tr.XPDR_ID = st.XPDR_ID
WHERE
tr.XPDR_ID = 1
AND (st.TRACE_TYPE = 'myType')
AND (st.TRACE_ANSWER_TYPE = 0
      OR st.TRACE_ANSWER_TYPE = 4
      OR st.TRACE_ANSWER_TYPE = 3
      OR st.TRACE_ANSWER_TYPE = 5)
AND (si.SITE_NAME = 'Paris')
AND (mc.CHAIN_NAME = 'myChain')

Donc pour parler plus clairement je voudrais récupérer toutes les lignes de la table T_TRACE qui correspondent a divers paramètres (nom du site, nom de la chain, nom de entity) --> Je récupère donc un paquet de ligne, et parmi celles ci, je voudrais celle qui a la date la plus récente (la sous requête avec MAX...)

Je précise que cette requête fonctionne, i.e elle me retourne le résultat attendu, mais j'ai le temps d'aller chercher un café et de le boire avant qu'elle me le donne..

Je précise également, que toutes les colonnes intervenant dans les jointures sont indexées.

Enfin, j'avais essayé avec un "ORDER BY TRACE_MEASURE_DATE DESC LIMIT 1" à la place de la sous requête, et c'était pas mieux...

Si qq'un peut me donner des billes, ça serait bien cool !

[Maljuna Kris]

Saluton,
Pour la lisibilité je remplacerais

Code mysql :

1
2
3
4
AND (st.TRACE_ANSWER_TYPE = 0
      OR st.TRACE_ANSWER_TYPE = 4
      OR st.TRACE_ANSWER_TYPE = 3
      OR st.TRACE_ANSWER_TYPE = 5)

par

Code mysql :

AND (st.TRACE_ANSWER IN (0,3,4,5)

.
Pour le reste, MySQL apprécie assez peu les sous-requêtes.
J'ajoute que la guerre des étoiles est parfois aussi très consommatrice de ressources.

[GyZmoO]

Yop merci pour la réponse.
Ok pour le IN, c'est plus lisible.

Pour la guerre des étoiles pas de prob, je sais J'ai juste mis ça en recopiant la requête pour tester (sinon dans le code, y'a juste les colonnes nécessaires ^^)

[CinePhil]

Justement, si tu donnais le vrai SELECT et la structure des tables, j'aurais peut-être une solution avec une jointure simple sur une sous-requête GROUP BY.

[GyZmoO]

Bonjour !

Voici le select :

Code :

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
 
EXPLAIN SELECT st.TRACE_ID, st.TRACE_VAL, st.XPDR_ID, st.TRACE_TYPE, st.TRACE_ANSWER_TYPE, st.TRACE_USER  FROM T_TRACE st
INNER JOIN T_MEASUREMENT_CHAIN mc ON mc.CHAIN_ID = st.CHAIN_ID
INNER JOIN T_SITE si ON si.SITE_ID = mc.SITE_ID
INNER JOIN T_ENTITY tr ON tr.XPDR_ID = st.XPDR_ID
 
WHERE
tr.XPDR_ID = 1
AND (st.TRACE_TYPE = 'myType')
AND (st.TRACE_ANSWER_TYPE = 0
      OR st.TRACE_ANSWER_TYPE = 4
      OR st.TRACE_ANSWER_TYPE = 3
      OR st.TRACE_ANSWER_TYPE = 5)
AND (si.SITE_NAME = 'Paris')
AND (mc.CHAIN_NAME = 'myChain')
AND st.TRACE_MEASURE_DATE =
 
(SELECT MAX(TRACE_MEASURE_DATE) FROM T_TRACE st
INNER JOIN T_MEASUREMENT_CHAIN mc ON mc.CHAIN_ID = st.CHAIN_ID
INNER JOIN T_SITE si ON si.SITE_ID = mc.SITE_ID
INNER JOIN T_ENTITY tr ON tr.XPDR_ID = st.XPDR_ID
WHERE
tr.XPDR_ID = 1
AND (st.TRACE_TYPE = 'myType')
AND (st.TRACE_ANSWER_TYPE IN (0,3,4,5))
AND (si.SITE_NAME = 'Paris')
AND (mc.CHAIN_NAME = 'myChain')

[GyZmoO]

La table T_TRACE :

Code :

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
 
CREATE TABLE `t_trace` (
  `TRACE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `TRACE_MEASURE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `TRACE_TYPE` enum('Spect', 'Statistics', 'Inside', 'Outside') DEFAULT NULL,
  `TRACE_USER` varchar(40) DEFAULT NULL,
  `TRACE_CENTER_FREQ` decimal(15,6) NOT NULL,
  `TRACE_BANDWIDTH` decimal(15,6) NOT NULL,
  `TRACE_RBW` decimal(15,6) NOT NULL,
  `TRACE_VBW` decimal(15,6) NOT NULL,
  `TRACE_VBW_AUTO` tinyint(1) NOT NULL,
  `TRACE_RBW_AUTO` tinyint(1) NOT NULL,
  `TRACE_REF_LEVEL` float NOT NULL DEFAULT '-20',
  `TRACE_NB_DB_DIV` float NOT NULL DEFAULT '10',
  `TRACE_TOTAL_POWER` float NOT NULL,
  `TRACE_TOTAL_EIRP` float NOT NULL,
  `TRACE_X_THRESHOLD` mediumtext,
  `TRACE_Y_THRESHOLD` mediumtext,
  `TRACE_TRACE_TYPE` int(11) NOT NULL,
  `TRACE_TRACE_COUNT` int(11) NOT NULL,
  `TRACE_Y_VAL` mediumblob,
  `TRACE_XPOL_ID` int(11) NOT NULL,
  `LINKED_SPECTRUM_ID` int(11) NOT NULL,
  `TRACE_ANSWER_TYPE` tinyint(1) NOT NULL,
  `TRACE_IS_REFERENCE` tinyint(1) NOT NULL,
  `XPDR_ID` smallint(6) DEFAULT NULL,
  `RF_ID` smallint(6) DEFAULT NULL,
  `CHAIN_ID` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`TRACE_ID`),
  KEY `IND_SPECT_DATE` (`TRACE_MEASURE_DATE`,`TRACE_TYPE`),
  KEY `IND_SPECT_XPDR_MEAS_DATE` (`XPDR_ID`,`TRACE_MEASURE_DATE`),
  KEY `IND_SPECT_CHAIN_MEAS_DATE` (`CHAIN_ID`,`TRACE_MEASURE_DATE`)
) ENGINE=InnoDB AUTO_INCREMENT=193298 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TRACE_ID)
(PARTITION p201136 VALUES LESS THAN (288822) ENGINE = InnoDB,
 PARTITION pLast VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Comme vous pouvez le voir il n'y a pas de FK, car la table est partitionnée.

La table T_MEASUREMENT_CHAIN

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE TABLE `t_measurement_chain` (
  `CHAIN_ID` smallint(6) NOT NULL AUTO_INCREMENT,
  `SITE_ID` smallint(6) NOT NULL,
  `CHAIN_NAME` varchar(40) NOT NULL,
  `CHAIN_MANUFACTURER` varchar(40) DEFAULT NULL,
  `CHAIN_MODEL` varchar(40) DEFAULT NULL,
  `CHAIN_TYPE` tinyint(4) NOT NULL,
  `CHAIN_ACTIVE` tinyint(1) NOT NULL,
  `CHAIN_UNWANTED_MAX_BAND` decimal(15,6) DEFAULT NULL,
  `CHAIN_MAX_SPAN` float DEFAULT NULL,
  `CHAIN_TV_TYPE` tinyint(1) NOT NULL,
  `CHAIN_NB_PORTS` int(11) DEFAULT NULL,
  PRIMARY KEY (`CHAIN_ID`),
  KEY `fk_measchain_sitename` (`SITE_ID`),
  CONSTRAINT `fk_measchain_sitename` FOREIGN KEY (`SITE_ID`) REFERENCES `t_site` (`SITE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

La table T_SITE :

Code :

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE `t_site` (
  `SITE_ID` smallint(6) NOT NULL AUTO_INCREMENT,
  `SITE_NAME` varchar(40) NOT NULL,
  `SITE_TCP_ADDRESS` varchar(20) NOT NULL,
  `SITE_TYPE` tinyint(4) NOT NULL,
  `SITE_LAST_DATA_AUTO_ARCHIVE` datetime DEFAULT NULL,
  `SITE_LAST_RESULT_AUTO_ARCHIVE` datetime DEFAULT NULL,
  PRIMARY KEY (`SITE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Et enfin T_ENTITY :

Code :

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
 
CREATE TABLE `t_entity` (
  `XPDR_ID` smallint(6) NOT NULL AUTO_INCREMENT,
  `SAT_ID` smallint(6) NOT NULL,
  `XPDR_NAME` varchar(40) NOT NULL,
  `XPDR_LABEL_NAME` varchar(40) NOT NULL,
  `EXTERNAL_XPDR_NAME` varchar(40) NOT NULL,
  `XPDR_COMMENT` varchar(255) DEFAULT NULL,
  `XPDR_LAST_UPDATE` timestamp NULL DEFAULT NULL,
  `XPDR_GLOBAL_POLICY` tinyint(4) NOT NULL,
  `XPDR_TYPE` int(11) NOT NULL,
  `RF_ID` smallint(6) DEFAULT NULL,
  `CUST_ID` smallint(6) DEFAULT NULL,
  `BEAM_ID` smallint(6) NOT NULL,
  `SPECT_ID` int(11) DEFAULT NULL,
  `UPXPDR_ID` smallint(6) DEFAULT NULL,
  `XPDR_STATUS` tinyint(4) NOT NULL DEFAULT '2',
  `XPDR_LAST_SYNCHRO_DATE` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`XPDR_ID`),
  KEY `fk_xpdr_uplinkxpdr` (`UPXPDR_ID`),
  KEY `fk_xpdr_beam` (`BEAM_ID`),
  KEY `fk_xpdr_custid` (`CUST_ID`),
  KEY `fk_xpdr_rfpath` (`RF_ID`),
  KEY `fk_xpdr_satcode` (`SAT_ID`),
  KEY `IND_XPDR` (`XPDR_NAME`,`SAT_ID`,`XPDR_TYPE`),
  CONSTRAINT `fk_xpdr_beam` FOREIGN KEY (`BEAM_ID`) REFERENCES `t_beam` (`BEAM_ID`),
  CONSTRAINT `fk_xpdr_custid` FOREIGN KEY (`CUST_ID`) REFERENCES `t_customer` (`CUST_ID`),
  CONSTRAINT `fk_xpdr_rfpath` FOREIGN KEY (`RF_ID`) REFERENCES `t_rfp` (`RF_ID`),
  CONSTRAINT `fk_xpdr_satcode` FOREIGN KEY (`SAT_ID`) REFERENCES `t_sata` (`SAT_ID`),
  CONSTRAINT `fk_xpdr_uplinkxpdr` FOREIGN KEY (`UPXPDR_ID`) REFERENCES `t_entity` (`XPDR_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=827 DEFAULT CHARSET=utf8

[CinePhil]

Comme tu ne veux que les colonnes de la table T_TRACE pour la ligne qui correspond à la date max selon tes critères de restriction, regarde si cette requête convient :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT st1.TRACE_ID, st1.TRACE_VAL, st1.XPDR_ID, st1.TRACE_TYPE, st1.TRACE_ANSWER_TYPE, st1.TRACE_USER
FROM T_TRACE st1
INNER JOIN
(
    SELECT tr.XPDR_ID, mc.CHAIN_ID, MAX(st.TRACE_MEASURE_DATE) AS Date_max
    FROM T_TRACE st
    INNER JOIN T_MEASUREMENT_CHAIN mc ON mc.CHAIN_ID = st.CHAIN_ID
        INNER JOIN T_SITE si ON si.SITE_ID = mc.SITE_ID
    INNER JOIN T_ENTITY tr ON tr.XPDR_ID = st.XPDR_ID
    WHERE tr.XPDR_ID = 1
        AND st.TRACE_TYPE = 'myType'
        AND st.TRACE_ANSWER_TYPE IN (0,3,4,5)
        AND si.SITE_NAME = 'Paris'
        AND mc.CHAIN_NAME = 'myChain'
    GROUP BY st.XPDR_ID, st.CHAIN_ID
) tmp 
    ON tmp.XPDR_ID = st1.XPDR_ID
        AND tmp.CHAIN_ID = st1.CHAIN_ID

Je regroupe avec les colonnes de T_TRACE figurant dans les conditions de jointure et je pense que les conditions de restriction devraient limiter les résultats à ce que tu souhaites. Ensuite, j'utilise ces colonnes dans la jointure avec T_TRACE pour récupérer les autres colonnes souhaitées.

[GyZmoO]

Merci pour la réponse, je checke ça de suite et te tiens au courant !

[GyZmoO]

Re !

Alors j'ai testé ta requête CinePhil, malheureusement, elle me renvoie toutes les lignes correspondant au doublet (xpdr_id, chain_id) et non pas juste celle ayant la date la plus récente :'(

J'ai testé à part la sous requête (alias tmp), et elle, elle fonctionne correctement (renvoie bien la date max pour le double (xpdr_id, chain_id) !!

[CinePhil]

Effectivement, j'ai oublié de mettre la restriction sur la date = date_max !

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT st1.TRACE_ID, st1.TRACE_VAL, st1.XPDR_ID, st1.TRACE_TYPE, st1.TRACE_ANSWER_TYPE, st1.TRACE_USER
FROM T_TRACE st1
INNER JOIN
(
    SELECT tr.XPDR_ID, mc.CHAIN_ID, MAX(st.TRACE_MEASURE_DATE) AS Date_max
    FROM T_TRACE st
    INNER JOIN T_MEASUREMENT_CHAIN mc ON mc.CHAIN_ID = st.CHAIN_ID
        INNER JOIN T_SITE si ON si.SITE_ID = mc.SITE_ID
    INNER JOIN T_ENTITY tr ON tr.XPDR_ID = st.XPDR_ID
    WHERE tr.XPDR_ID = 1
        AND st.TRACE_TYPE = 'myType'
        AND st.TRACE_ANSWER_TYPE IN (0,3,4,5)
        AND si.SITE_NAME = 'Paris'
        AND mc.CHAIN_NAME = 'myChain'
    GROUP BY st.XPDR_ID, st.CHAIN_ID
) tmp 
    ON tmp.XPDR_ID = st1.XPDR_ID
        AND tmp.CHAIN_ID = st1.CHAIN_ID
WHERE st1.TRACE_MEASURE_DATE = tmp.Date_max

Peut-être faudra t-il aussi finalement reprendre les autres restrictions de la sous-requête dans la requête principale.

[GyZmoO]

Bon, ça cartonne, ça fonctionne, et ça va vite !

Merci beaucoup à toi CinePhil ainsi qu'a Maljuna Kris de m'avoir dépanné !

[Maljuna Kris]

Envoyé par CinePhil

Effectivement, j'ai oublié de mettre la restriction sur la date = date_max !

Pourquoi ne pas en faire un des critères de la jointure plutôt que d'ajouter une clause WHERE ?

[CinePhil]

Envoyé par Maljuna Kris

Pourquoi ne pas en faire un des critères de la jointure plutôt que d'ajouter une clause WHERE ?

Je pense que ça revient au même et que la requête s'exécutera de la même façon. Et je préfère séparer les conditions de jointure et les conditions de restriction, sauf dans le cas d'une condition de restriction sur une table de droite dans une jointure externe gauche bien sûr !

[Maljuna Kris]

Envoyé par CinePhil

Je pense que ça revient au même et que la requête s'exécutera de la même façon. Et je préfère séparer les conditions de jointure et les conditions de restriction, sauf dans le cas d'une condition de restriction sur une table de droite dans une jointure externe gauche bien sûr !

Sauf que là (et sauf ton respect, Cinéphil,), de mon point de vue, c'est bien une condition de la jointure.

[CinePhil]

Envoyé par Maljuna Kris

Sauf que là (et sauf ton respect, Cinéphil,), de mon point de vue, c'est bien une condition de la jointure.

Euh... oui, finalement, à y regarder de plus près... tu as raison.
J'ai dû être perturbé par des trucs plus compliqués... après la pause déjeuner ça va mieux !