Discussion :

[Patrice75014]

Bonjour à tous

A partir de relevé journalier de compteur d'imprimantes effectué par des requetes SNMP, je constitue une base de données

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE `test` (
  `Last check` datetime NOT NULL,
  `Mac Address` varchar(17) NOT NULL,
  `Engine count` mediumint(9) default NULL,
  PRIMARY KEY  (`Last check`,`Mac Address`),
  KEY `Last check` (`Last check`),
  KEY `Mac Address` (`Mac Address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

`Last check` est la date et l'heure de la requete SNMP, `Mac Address` l'adresse Mac de l'imprimante et `Engine count` le compteur de pages imprimées
je cherche à établir une consommation journalière, par différence des compteurs N à N-1, pour une même adresse Mac
J'ai établi une requete qui me permet de sortir ce résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT o.`Mac Address` , o.`Last check` , o.`Engine count` - po.`Engine count` 
FROM test o
LEFT JOIN test po ON ( o.`Mac Address` = po.`Mac Address` ) 
WHERE po.`Last check` 
IN (
 
SELECT MAX( tmp.`Last check` ) 
FROM test tmp use index (`Last check`,`Mac Address`)
WHERE tmp.`Last check` < o.`Last check` 
AND tmp.`Mac Address` = o.`Mac Address` 
)
ORDER BY `o`.`Mac Address` ASC  
limit 30;

J'ai cependant un gros problème de performance. En effet, avec environ 3000 lignes dans la table, la requete dure 15s. avec 65 000 lignes dans la table, la requete dure plusieurs heures.
pour exemple, j'ai limité à 30 résultats, avec un EXPLAIN

30 lignes (0.475 s)

id? select_type? table? type? possible_keys? key? key_len? ref? rows? Extra?
1 PRIMARY o index Mac Address Mac Address 53 NULL 68237
1 PRIMARY po ref Mac Address Mac Address 53 imprimantes.o.Mac Address 38 Using where
2 DEPENDENT SUBQUERY tmp ref Last check,Mac Address Mac Address 53 imprimantes.o.Mac Address 35 Using where; Using index

Qui peut m'aider à optimise ma requete et/ou ma table ?
Ou une autre méthode ?

Encore à tous pour votre aide

Patrice

[aieeeuuuuu]

Bonjour,

qu'est ce que donne votre requete si vous inversez l'ordre des colonne de la clef primaire (ou que vous créez un index (`Mac Address`, `Last check`) et que vous supprimez votre USE INDEX) ?

[Patrice75014]

Merci
Quand j'enleve le "USE INDEX", la transaction s'execute en 1,294s
Je suis à 1,075s en inversant les champs et 0,675s par création d'un index avec les 2 champs à la place de la clef primaire

[aieeeuuuuu]

Il faudra en effet un index adéquat pour supporter la requête. Donc soit inverser l'ordre des colonnes de la clef primaire si cela n'impacte pas d'autres requêtes, soit créer un nouvel index, en incluant la colonne `Engine count` pour qu'il soit couvrant :

Code :

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

1
2
 
CREATE UNIQUE INDEX IX_test ON test(`Mac Address`, `Last check` , `Engine count`)

Vous pouvez aussi essayer cette requête :

Code :

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

1
2
3
4
5
6
7
SELECT  a.`Mac Address`, MAX(b.`Last check`), MAX(b.`Engine count`) - a.`Engine count`
FROM test a
INNER JOIN test b
    ON b.`Mac Address` = a.`Mac Address`
	AND b.`Last check` > a.`Last check`
GROUP BY a.`Mac Address`, a.`Last check`
HAVING COUNT(*) = 1

Attention, les imprimantes n'ayant qu'une seule ligne ne ressortiront pas. Si cela est gênant, il faudrait les ajouter spécifiquement avec un UNION ALL qui aura un cout non négligeable :

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
 
SELECT  a.`Mac Address`, MAX(b.`Last check`), MAX(b.`Engine count`) - a.`Engine count`
FROM test a
INNER JOIN test b
    ON b.`Mac Address` = a.`Mac Address`
	AND b.`Last check` > a.`Last check`
GROUP BY a.`Mac Address`, a.`Last check`
HAVING COUNT(*) = 1
UNION ALL 
SELECT a.`Mac Address`, a.`Last check`, 0
FROM test a
WHERE not exists(
	SELECT 1
	FROM test b
	WHERE b.`Mac Address` = a.`Mac Address`
	AND b.`Last check`> a.`Last check`
)

[aieeeuuuuu]

ou une alternative peut-être moins couteuse pour prendre en compte les imprimantes avec une seule ligne, mais qui suppose que le compteur s'incrémente ou ne change pas (le résultat serait faux dans le cas d'une "consommation négative", mais dans votre cas, ça ne semble pas possible)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT `Mac Address`, `Last check`,  MAX(conso)
FROM (
SELECT  a.`Mac Address`, MAX(b.`Last check`) AS `Last check`, MAX(b.`Engine count`) - a.`Engine count` as Conso
FROM test a
INNER JOIN test b
    ON b.`Mac Address` = a.`Mac Address`
	AND b.`Last check` >= a.`Last check`
GROUP BY a.`Mac Address`, a.`Last check`
HAVING COUNT(*) < 3) AS T
GROUP BY `Mac Address`, `Last check`

[Patrice75014]

Merci
Effectivement, les requetes s'executent mais ne ramene pas le bon résultat
je souhaite comparer les compteurs N et N-1 établis lors des scans J et J-1
par exemple, si j'avais 3 jours de scan avec 2 imprimantes détectées, j'aurais dans le fichier initial (table Test) 3x2=6 enregistrements
la requete qui me compare devrait me ramener 4 enregistrements (soit le nombre d'imprimantes x le nb de jours de scan-1)
Hors la requete proposée me ramene uniquement une ligne par imprimante

[aieeeuuuuu]

Ah, j'avais mal compris, je pensais que vous ne vouliez que le dernier écart.

Donc :

Toujours si l'on suppose que la quantité est forcément stable ou criossante, vous pouvez faire comme ceci :

Code :

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

1
2
3
4
5
6
7
 
SELECT a.`Mac Address`, a.`Last check`, a.`Engine count` - MAX(b.`Engine count`)
FROM test a
LEFT JOIN test b
	ON b.`Mac Address` = a.`Mac Address`
	AND b.`Last check` < a.`Last check`	
GROUP BY a.`Mac Address`, a.`Last check`, a.`Engine count`

Idéalement, il aurait fallu traiter ce problème avec des fonctions analytiques, mais celles-ci ne sont pas disponible sous MySQL.
Voici donc une alternative moins académique, mais qui peut donner de bons résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT  
	a.`Mac Address`, 
	a.`Last check`, 
	a.`Engine count` - (
		SELECT `Engine count` 
		FROM test b 
		WHERE b.`Mac Address` = a.`Mac Address`
		AND b.`Last check` < a.`Last check`
		ORDER BY `Last check` DESC
		LIMIT 1
)
FROM test a

[Patrice75014]

je m'incline et je dis :"chapeau" !
Cela fonctionne
Encore merci