Discussion :

[eracius]

Bonjour, je travaille sur une application J2EE (Spring/Hibernate) qui gère une base de données assez simple et qui normalement ne sera pas énorme. J'ai tout de même rempli ma table historique avec 5 millions d'entrées pour faire des tests de fiabilité et voir un peu comment réagit mon programme. Cette table reçoit des relevés de valeurs à intervalles réguliers et est donc vouée à grossir continuellement, d'où un besoin de performance (même si je n'attendrais pas 5 millions d'entrée avant 10 ans)

Voilà la table (exportation phpmyadmin) :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE `history` (
  `id` int(11) NOT NULL auto_increment,
  `idnode` varchar(30) NOT NULL,
  `idsensor` varchar(30) NOT NULL,
  `date` bigint(30) NOT NULL,
  `value` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `date` (`date`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5693085 ;

Je fais sur cette table la requête SELECT suivante qui prend environ 7 secondes (mes dates sont stockées en millisecondes)

Code :

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

1
2
3
4
5
6
 
SELECT * FROM `history` 
WHERE `idnode` = '000D6F00000B4E3E' 
AND `date` > 1211752800000 
AND `date` < 1211794628000 
ORDER BY id

Lorsque je mets le ORDER BY en DESC, la requête prends 9 secondes. Lorsque je mets le ORDER BY sur mon champs date, elle prend 25 secondes (asc ou desc).

Je ne suis pas du tout un spécialiste du SQL mais quand je vois que de menus changements peuvent provoquer autant de différences de performance, je me dis qu'il y a surement moyen de faire beaucoup mieux.

Si vous avez des idées pour optimiser cette requête, mettre en lumière des incohérences de construction de ma table où autre qui pourrait m'aider à gagner en puissance, je prends.

Merci d'avance.

[ced]

Bonjour,

Première piste d'optimisation, utiliser le mot-clef BETWEEN plutôt que 2 conditions "<" et ">" :

Code :

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

1
2
3
4
SELECT * FROM `history` 
WHERE `idnode` = '000D6F00000B4E3E' 
AND `date` BETWEEN 1211752800000 AND 1211794628000 
ORDER BY id

ced

[eracius]

Merci pour ta réponse, j'ai testé et ça ne change pratiquement rien (en fait ma requête Hibernate était un between)

[nounetmasque]

Essaye en metant un index sur ton champ idnode.

[eracius]

Alors je vous livre mes derniers tests qui laissent planer quelques doutes :

Tout d'abord quelques précisions concernant ma table. Je l'ai bourrinée pour arriver à plus de 5 millions d'enregistrements en entrant toujours le même node. Il y a donc 5 millions de lignes qui ont comme idnode NM_00, plus environ 2000 entrées qui ont un identifiant "classique" pour moi, soit un code de se type : 000D6F00000B4E3E

J'ai testé cette requête SELECT sur les deux cas :

Code :

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

1
2
 Requête 1 : 
SELECT * FROM `history` WHERE `idnode` = 'NM_00' AND date BETWEEN 1211752800000 AND 1211796908000;

Elle rend un résultat en 0.0469 sec (pour 747 254 lignes correspondantes)

Code :

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

1
2
 Requête 2 : 
SELECT * FROM `history` WHERE `idnode` = '000D6F00000B4E3E' AND date BETWEEN 1211752800000 AND 1211796908000;

Elle rend un résultats 3.1978 sec (pour 199 lignes correspondantes)

Soit des résultats assez étranges, la requête sur un idnode comportant moins d'entrée étant plus longue.

J'ai ajouté l'index sur idnode comme me le suggérait très justement le monsieur si dessus.

J'obtiens alors :

Requête 1 : 21.4225 sec pour 747 254 lignes correspondantes

Requête 2 : 0.0309 sec pour 199 lignes correspondantes

Je précise que je n'ai pas enlevé l'index sur date, quand je l'enlève ça ne change presque rien.

Ma question est donc, pourquoi sans l'index mes résultats sont-ils inversés ? Est-ce que le fait que mon idnode 000D6F00000B4E3E soit une String plus longue pénalise mon traitement contrairement à NM_00 ? A l'inverse, l'indexation pénalise le SELECT sur l'idnode qui comporte 5 millions d'entrées dans la base.

Quel est réellement le fonctionnement de l'index ?

Merci d'avance pour votre aide.