Discussion :

[Invité]

Bonjour à tous,

Je n'arrive pas à obtenir des performances sur cette 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
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
SELECT
	C1.IDVersions_Parcs,
	TI.Libellé AS LibTypeCompteur,
	C1.IDCompteurs AS IDCompteursJour,
	C1.Date AS DateJour,
	CAST(C1.Valeur AS UNSIGNED) AS ValeurJour,
	C2.IDCompteurs AS IDCompteursPrécédent,
	C2.Date AS DatePrécédente,
	CAST(C2.Valeur AS UNSIGNED) AS ValeurPrécédente,
	CONCAT(C1.IDCompteurs,C1.Date,C2.Date) AS Tri
FROM
	Compteurs C1
	INNER JOIN TypesInformation TI ON TI.IDTypesInformation=C1.IDTypesInformation
	INNER JOIN Compteurs C2 ON (
		C2.IDTypesInformation=C1.IDTypesInformation
		AND C2.IDVersions_Parcs=C1.IDVersions_Parcs
		AND C2.Date=(
			SELECT
				MAX(Date)
			FROM
				Compteurs
			WHERE
				Date<C1.Date
				AND IDTypesInformation=C1.IDTypesInformation
				AND IDVersions_Parcs=C1.IDVersions_Parcs
		)
	)
WHERE
	TI.IDNaturesInformation=2
	AND C1.Date>='2012-02-01'
	AND CAST(C1.Valeur AS UNSIGNED)>CAST(C2.Valeur AS UNSIGNED)
ORDER BY
	IDCompteursJour,
	DateJour DESC,
	DatePrécédente DESC
LIMIT
	1000

Le but étant de récupérer les compteurs pour lesquels, à la date immédiatement inférieure, la valeur est supérieure.
J'ai eu beau la tourner dans tous les sens ... elle est (très) lente.

Merci pour votre aide

[ericd69]

salut,

tu as bien indexé:

• sur TypesInformation: IDTypesInformation
• sur Compteurs:
  
  • IDTypesInformation
  • Date

?

[Invité]

Bonjour,

Merci pour ton aide.

IDTypesCompteur est la primary de TypesCompteur est c'est la foreign key de compteurs (donc indexée)
Compteurs.Date est également indexée.

Tu penses qu'en remplaçant

Code :

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

1
2
3
4
5
6
7
8
			SELECT
				MAX(Date)
			FROM
				Compteurs
			WHERE
				Date<C1.Date
				AND IDTypesInformation=C1.IDTypesInformation
				AND IDVersions_Parcs=C1.IDVersions_Parcs

Par

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
			SELECT
				Date
			FROM
				Compteurs
			WHERE
				Date<C1.Date
				AND IDTypesInformation=C1.IDTypesInformation
				AND IDVersions_Parcs=C1.IDVersions_Parcs
			ORDER BY
				Date DESC
			LIMIT
				1

Je pourrais gagner un peu de perf ?

[ericd69]

non pas du tout...

limit ne fait que limiter le nombre de ligne selon des critères mais après la génération de toutes les lignes possibles...

l'order by lui rajoute une séquence de tri...

donc tu vois y a pas de risque que ça arrange quelque chose

tu es obligé de caster dans le where?

les clés étrangères ne sont indexées que si tu as des tables innodb sinon vérifie bien... car c'est pas automatique en myisam il me semble...

[Invité]

Re,

Le cast est nécessaire car Compteurs.Valeur est un tinytext et je veux une comparaison numérique et pas alpha.

Les tables sont en INNODB.

Cordialement,

[ericd69]

la valeur stockée dedans est numérique?

si oui n'est-il pas intéressant de les stocker directement en unsigned? histoire de virer les conversions lors du traitement...

[Invité]

Si je n'avais que des numériques à stocker, crois-tu que j'utiliserais du tinytext
Mais ce cast à l'air de te perturber...
J'ai donc fait le test
J'ai 2 BD identiques. Une en test et une en prod. Les structures sont identiques et le serveur également.
1) Prod : Nb enr Compteurs = 3 784 => 0.547s
2) Test : Nb enr Compteurs = 113 850 => 54.563s

En supprimant le cast
1) 0.422s
2) 59.625s

Le cast n'est pas en cause. Par contre le temps est exponentiel. Je multiplie par 30 le nb d'enr et par 100 le temps de traitement.

Et j'ajoute que Compteurs.Valeur est également indexée sur 100 caractères.

[ericd69]

100 caractères...

si tu as une valeur non numérique au départ n'est pas possible de scinder les 2 en 2 colonnes distinctes?

varchar est peut-être plus optimisé que tinytext... les index sont pas gérés pareils pour les 2 types (dérivés de char ou de text)... avec text les index utilisent min(largeur de colonne, taille maximal d'indexation sur text)

de plus, cale la taille de ta colonne valeur au plus petit possible...

tu as testé l'implémentation des index sur valeur avec une autre forme (tu en as 2 formes en innodb)?

[Invité]

3 types d'index même BTREE, RTREE, HASH.
Je n'ai jamais utilisé ces types. J'ai tjs laissé par défaut...
Aucune idée de l'incidence...

Scindé, c'est pas très "normale"
Et je ne suis pas convaincu que cela change quoique ce soit
J'ai l'impression que c'est la sous requête dans le FROM qui se traine.
Mais pquoi ?

J'ai tenté un EXPLAIN mais je ne suis pas assez aguerri pour y détecter une quelconque idée d'optimisation.

Rrrrraaaaahh, ch'uis déçu.
Je pensais que ce forum était plein de caïds, de cracks, d'intégristes du SQL.
Bah non !
Eric, prends pas ça pour toi STP. T'es le seul à essayer de m'aider

[ericd69]

disons que scinder pourrais permettre de mieux indexer...

un index sur un dérivé de text ou char ne sera jamais performant (car tributaire du charset et les algorithmes de comparaison de chaines sont à la ramasse en terme de performance par rapport à une comparaison de valeurs numériques)

c'est simple à comprendre:

• pour identifier un caractère (ou une séquence pour le multibytes comme utf8) tu lis octet par octet... donc tu accomplit ça sur chaque chaine à comparer jusqu'à min(largeur de colonne de type char ou text ou leur dérivés, taille maximale d'indexation sur text définie dans la configuration de mysql)
• selon la taille tu lis et compare directement en 1 à 3 accès maximum (type double ou int sur 11 octets)

pas besoin de te faire un dessin pourquoi on déconseille toujours un index sur des caractère sauf besoin impérieux...

d'où ma question sur la modélisation de tes données...
beaucoup de gens, quand ils brasses des csv ou import quelconques (comme je subodore que c'est le cas avec toi) ne pensent qu'à faire des traitements directe sur la table obtenue...

pour moi, un bon modèle de données revient à stocker de manière la plus compacte et performante ce genre de données, pour faire des recherches réellement optimisées... et quitte à refaire quelques jointures ou traitements pour restituer les résultats dans le format de départ... ça nécessite parfois plus de réflexion en amont mais c'est, à mon avis toujours la meilleure approche...
en gros dé-corréler les données de leur forme stockée...

exemple le code postale:

• 3 caractères alphanumérique pour le département
• 3 chiffres pour la commune

il peut être intéressant de séparer les 2 pour une recherche plus efficace et de remplacer par un tinyint le code département dans la table des communes:

• table plus compacte
• recherche ou jointure plus rapide avec un tinyint

donc tu peux réfléchir à cette approche

oui, elle grève la recherche car elle est refaite à chaque ligne de jointure

le problème c'est que les 3 requêtes s'entremêlent, donc dur de faire une plus grosse sous- requête servant dans un super inner à la place...

[CinePhil]

Quelle est la clé primaire de la table Compteurs ?

Et plus généralement, quelle est la structure complète des tables impliquées dans la requête (résultat de SHOW CREATE TABLE la_table) ?

[Invité]

Bonjour.

Je me réponds à moi même au cas où cela servirait à quelqu'un.

Je suis d'abord parti du postulat que MAX(IDInformations) et MAX(Date) renvoyait le même enregistrement. Puis, j'ai créé une vue :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE VIEW vueTmpDerniersInformations AS
SELECT
	MAX(I1.IDInformations) AS IDInformationsMax,
	V_M.IDMachines,
	I1.IDTypesInformation
FROM
	Informations I1
	INNER JOIN Versions_Machines V_M ON V_M.IDVersions_Machines=I1.IDVersions_Machines
	INNER JOIN TypesInformation TI ON TI.IDTypesInformation=I1.IDTypesInformation
GROUP BY
	V_M.IDMachines,
	I1.IDTypesInformation
;

Et je l'utilise dans ma requête principale.
Résultat : De plus d'une minute (113 000 enr), je suis passé à 0.5 secondes.

Merci à CinePhil & ericd69