Problème d'index ignoré sur des intervalles de dates
Bonjour,
Je viens solliciter votre aide, car il me semble que MySQL ne tiens pas compte de la valeur "date" de mon index composite, lorsque je l'utilise avec un intervale.
Mon problème fait suite aux difficultés de mises en place de cette fameuse table:
http://www.developpez.net/forums/d13...itation-table/
Il s'agit d'une table d'historique qui sauvegarde l'état de chaque variable d'un appareil.
Ces différents types de variables possibles sont enregistré dans une table à part:
Code:
1
2
3
4
5
6
7
|
VALEUR
(
ValeurId INT NOT NULL,
ValeurNom VARCHAR(64) NOT NULL
)
PRIMARY KEY (ValeurId) ; |
Il existe 339 types de valeurs possibles.
Et voici la table qui sauvegarde les valeurs:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
APPAREIL_VALEUR
(
AppareilId INT NOT NULL,
ValeurId INT NOT NULL,
ValeurDate TIMESTAMP NOT NULL,
ValeurString VARCHAR(32) NOT NULL,
ValeurWrite TINYINT(1) NOT NULL,
PRIMARY KEY (AppareilId, ValeurId, ValeurDate),
FOREIGN KEY (AppareilId) REFERENCES APPAREIL,
FOREIGN KEY (ValeurId) REFERENCES VALEUR
); |
En plus de l'index créé par la clef primaire (AppareilId, ValeurId, ValeurDate), un deuxième index existe pour gérer les fameuses valeurs en mode écriture:
(AppareilId, ValeurWrite, ValeurId, ValeurDate)
Et comme un mauvais dessin vaut mieux qu'un long discourt, je remet également un exemple type de jeu de données à sauvegarder:
+----------------------+--------------+------------------+-------------+
| ValeurNom | ValeurString | ValeurDate | ValeurWrite |
+----------------------+--------------+------------------+-------------+
| Consigne à atteindre | 20 | 15/04/2013 18:30 | oui |
| Sonde 1 | 15 | 15/04/2013 18:15 | non |
| Consigne à atteindre | 15 | 15/04/2013 18:00 | non |
| Sonde 2 | 300 | 15/04/2013 17:00 | non |
| Erreur sonde 2 | oui | 15/04/2013 17:00 | non |
| Nom de la sonde 1 | Toto | 15/04/2013 17:00 | oui |
| ... | | | |
+----------------------+--------------+------------------+-------------+
Le problème survient lorsqu'il est nécéssaire de récupérer un ensemble de valeurs entre deux dates.
Par exemple, pour connaitre les valeurs moyenne, maxi et mini:
Code:
1
2
3
4
5
6
7
8
9
|
SELECT AVG(ValeurString +0.0) as avg, MIN(ValeurString +0.0) AS min, MAX(ValeurString +0.0) AS max, v.ValeurNom
FROM APPAREIL_VALEUR AS av
INNER JOIN VALEUR AS v
ON v.ValeurId = av.ValeurId
WHERE av.AppareilId = 289
AND av.ValeurWrite IS FALSE
AND av.ValeurDate BETWEEN '2013-06-01 00:00:00' AND '2013-06-02 00:00:00'
GROUP BY av.AppareilId, av.ValeurWrite, av.ValeurId |
(Les +0.0 ne sont pas très propres, mais permettent d'utiliser MIN et MAX sur les valeurs représentées, c'est à dire integer ou float, et non les string.)
La table contient plus des 100 millions de valeurs.
Cette requète, exécutée sur un ancien appareil (plus de 2 millions de valeurs à lui seul) peu prendre jusqu'à 40 secondes !
Voici l'explain associé:
+----+------------+-----------+--------+----------------------+-------------+-------------------------+----------+---------------------------------------+
| id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+------------+-----------+--------+----------------------+-------------+-------------------------+----------+---------------------------------------+
| 1 | PRIMARY | av | range | PRIMARY, idx_valeurs | idx_valeurs | 3 | NULL | 2420378 | Using where |
| 1 | PRIMARY | v | eq_ref | PRIMARY | PRIMARY | 4 | av.AppareilId | 1 | |
+----+------------+-----------+--------+----------------------+-------------+-------------------------+----------+---------------------------------------+
Celà me fait penser que la requète exécute un fullscan de la table.
Si je modifie la requète de façon à ne plus utiliser BETWEEN mais une simple égalité, le résultat est radicalement différent:
Code:
1
2
3
4
5
6
7
8
9
|
SELECT AVG(ValeurString +0.0) as avg, MIN(ValeurString +0.0) AS min, MAX(ValeurString +0.0) AS max, v.ValeurNom
FROM APPAREIL_VALEUR AS av
INNER JOIN VALEUR AS v
ON v.ValeurId = av.ValeurId
WHERE av.AppareilId = 289
AND av.ValeurWrite IS FALSE
AND av.ValeurDate = '2013-06-01 00:00:00'
GROUP BY av.AppareilId, av.ValeurWrite, av.ValeurId |
+----+------------+-----------+--------+----------------------+-------------+----------------------------------+----------+----------------------------------------------+
| id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+------------+-----------+--------+----------------------+-------------+----------------------------------+----------+----------------------------------------------+
| 1 | PRIMARY | v | index | PRIMARY | ValeurNom | 42 | NULL | 339 | Using index, Using temporary, Using filesort |
| 1 | PRIMARY | av | range | PRIMARY, idx_valeurs | PRIMARY | 11 | const,v.ValeurId,const | 1 | Using where |
+----+------------+-----------+--------+----------------------+-------------+----------------------------------+----------+----------------------------------------------+
D'ailleurs, un troisième test en utilisant un interval d'une seconde ramène le nombre de rows à 2420378 (l'explain est alors le même que celui du premier exemple)
BETWEEN '2013-06-01 22:00:00' AND '2013-06-01 22:00:01'
(Et tout ça pour sortir toujours un résultat vide... ironie...)
Alors qu'est ce que j'ai loupé ?
Il semble que l'index soit totalement ignoré pour gérer l'intervalle de valeurs demandées (car il est bien utilisé pour ne sélectionner que les valeurs de l'appareil recherché).
Est-ce une mauvaise utilisation ? Une limitation de MySQL ?
