Où mettre mes index ?

**shkyo** · 31/08/2006, 09h29

Bonjour à tous,

Je suis en pleine réflexion avant de créer une BD MySQL5 de type InnoDB qui va stocker des mesures de type pressions, températures, densités, débits, etc ... et ce pour une vingtaine d'appareils industriels, ce qui donne environ 150 valeurs différentes, ma BD ne va donc contenir que des chiffres et des dates.

Et j'ai prévu de faire une table par type de valeurs, sachant que cela va me donner environ 100000 enregistrements/mois.

L'exploitation des données se fera avec des constructions de graphs en live avec PHP5 et avec des requêtes qui permettront d'extraire par ex les pressions et les températures des appareils A, B et C sur les 5 derniers jours (dans ce cas, j'aurais 1 valeur par heure pour chaque appareil).

En ce qui concerne les index, j'ai lu qu'il fallait en mettre sur les colonnes les plus utilisées, je pense donc en mettre sur les appareils les plus surveillés habituellement, mais dans mon cas, les valeurs vont être "plus ou moins" identiques, je précise, par ex pour un appareil donné qui fonctionne à 1500mbar en moyenne, les valeurs vont toujours s'étaler entre 1300 et 1700, un index sera-t-il efficace dans ce cas ??

Merci d'avance de vos avis...

**KiLVaiDeN** · 31/08/2006, 09h37

Salut,

Un index ne sera efficace que lorsque tu as répétition de valeurs;

Entre 1300 et 1700, si on ne prend que les entiers, ça fera 400 valeurs. Donc ton index aura "400" feuilles, et ta recherche ne se fera plus sur les 100 000 enreg, mais sur 400 uniquement, ce qui va être grandement accéleré.

Il faut faire le calcul des doublons, pour savoir si ça vaut la peine ou non d'indexer une colonne

A+

**Maximil ian** · 31/08/2006, 09h52

Heu par contre il faut bien voir que si l'optimiseur trouve la sélectivité de l'index trop basse pour une certaine valeur (ex : 90% des lignes de la table ont la valeur que tu recherches), il n'utilisera pas l'index.

Regarde aussi dans la FAQ : http://mysql.developpez.com/faq/?pag...miser_requetes

**shkyo** · 31/08/2006, 10h06

Envoyé par KiLVaiDeN

Salut,

Un index ne sera efficace que lorsque tu as répétition de valeurs;

Entre 1300 et 1700, si on ne prend que les entiers, ça fera 400 valeurs. Donc ton index aura "400" feuilles, et ta recherche ne se fera plus sur les 100 000 enreg, mais sur 400 uniquement, ce qui va être grandement accéleré.

Il faut faire le calcul des doublons, pour savoir si ça vaut la peine ou non d'indexer une colonne

A+

Calcul des doublons ? Et bien, pour un appareil donné, il va rester dans sa plage de valeurs habituelles à longueur de journée, donc je pense que les 4000 valeurs possibles (je vais avoir 1 chiffre après la virgule), en reprennant mon exemple, seront présentes au bout d'environ 2 à 3 mois d'enregistrements.

A partir de combien de doublons peut-on savoir si un index serait intéressant ?? Car je vais avoir ce phénomène pour pas mal de colonnes.

**Maximil ian** · 31/08/2006, 10h15

Envoyé par KiLVaiDeN

Il faut faire le calcul des doublons, pour savoir si ça vaut la peine ou non d'indexer une colonne

En fait si j'ai bien compris cette phrase, je suis moyennement d'accord. Ce qui prend le plus de temps c'est d'examiner les enregistrements sur le disque, pas de parcourir l'index. Une feuille qui va pointer vers 800 enregistrements n'est pas très efficace.

D'ailleurs, les index UNIQUE sont les plus performants.

**KiLVaiDeN** · 31/08/2006, 10h25

Hmm.. En fait pour moi l'index est uniquement interessant dans le cas de jointures, non ?

Une feuille qui va pointer vers 800 enregistrements n'est pas très efficace.

Ca reste quand même mieux que de parcourir 10 000 enregs pour savoir lesquels correspondent à une requête donnée, non ?

Au final, les index peuvent aider tout comme ils peuvent ne servir à rien; je ne crois pas qu'ils puissent "nuir" si il y a des doublons dans les colonnes, à moins que tu m'expliques la raison qui te ferait croire cela

Le mieux cependant, maintenant que je réfléchis un peu mieux au problème initial, serait d'avoir toutes les données nécessaires à ton calcul directement en clair dans tes colonnes de données, plutot que de les recalculer à chaque fois, c'est cela qui va grandement accelerer ton application, plutot que les indexs, qui à priori ne serviront pas à grand chose si tu ne fais aucune jointure ou order by.

A+

**shkyo** · 31/08/2006, 10h36

Envoyé par Maximilian

En fait si j'ai bien compris cette phrase, je suis moyennement d'accord. Ce qui prend le plus de temps c'est d'examiner les enregistrements sur le disque, pas de parcourir l'index. Une feuille qui va pointer vers 800 enregistrements n'est pas très efficace.

D'ailleurs, les index UNIQUE sont les plus performants.

Donc dans mon cas, je ne suis pas sur que ce soit très efficace de mettre des index, car pour un appareil donné et un type de valeur, je vais avoir 24 valeurs par jour, le calage d'extraction des données par les requêtes va se faire par une plage de dates.

Pour reprendre mon exemple en version SQL, les requêtes auront très souvent cette tête :

Code :

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

SELECT app_A,app_B,app_C FROM pressions WHERE dates='20060830'

ou encore :

Code :

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

SELECT app_A,app_B,app_C FROM pressions WHERE dates BETWEEN '20060825' and '20060830'

Qu'en pensez-vous ??

**Maximil ian** · 31/08/2006, 10h42

Envoyé par KiLVaiDeN

Hmm.. En fait pour moi l'index est uniquement interessant dans le cas de jointures, non ?

Non, aussi pour les colonnes de la clause WHERE, ORDER BY, GROUP BY...

Envoyé par KiLVaiDeN

Ca reste quand même mieux que de parcourir 10 000 enregs pour savoir lesquels correspondent à une requête donnée, non ?

Tout à fait d'accord

C'est juste qu'il est faux d'affirmer que plus il y a de valeurs identiques, plus l'index est efficace...

Envoyé par KiLVaiDeN

Le mieux cependant, maintenant que je réfléchis un peu mieux au problème initial, serait d'avoir toutes les données nécessaires à ton calcul directement en clair dans tes colonnes de données, plutot que de les recalculer à chaque fois, c'est cela qui va grandement accelerer ton application, plutot que les indexs, qui à priori ne serviront pas à grand chose si tu ne fais aucune jointure ou order by.

Oui, il peut être utile de créer des tables d'agrégats qui seront mises à jour régulièrement. Cependant :

- Les stats générées ne seront pas en temps réel
- Pour effectuer ces calculs, des index seront de toute façon indispensables...

**shkyo** · 31/08/2006, 10h42

Envoyé par KiLVaiDeN

Hmm.. En fait pour moi l'index est uniquement interessant dans le cas de jointures, non ?

Ca reste quand même mieux que de parcourir 10 000 enregs pour savoir lesquels correspondent à une requête donnée, non ?

Au final, les index peuvent aider tout comme ils peuvent ne servir à rien; je ne crois pas qu'ils puissent "nuir" si il y a des doublons dans les colonnes, à moins que tu m'expliques la raison qui te ferait croire cela

Le mieux cependant, maintenant que je réfléchis un peu mieux au problème initial, serait d'avoir toutes les données nécessaires à ton calcul directement en clair dans tes colonnes de données, plutot que de les recalculer à chaque fois, c'est cela qui va grandement accelerer ton application, plutot que les indexs, qui à priori ne serviront pas à grand chose si tu ne fais aucune jointure ou order by.

A+

??? Euh, et bien d'après moi, ce sera toujours en clair, sur une colonne de valeurs de type pression, je vais stocker par ex 1555.5 / 1541.9 / 1539.7 / 1539.4 / ... d'éventuels calculs pourront parfois être fait, mais uniquement APRES l'extraction des données dans mon code en PHP, que j'afficherait ensuite sous forme de graphs divers et variés.

**KiLVaiDeN** · 31/08/2006, 10h46

J'aurais juste un conseil par rapport à tes requêtes : tu utilises une colonne "String" on dirait ( SELECT app_A,app_B,app_C FROM pressions WHERE dates='20060830' ) hors, il vaut mieux utiliser des colonnes Date ou de type Integer/Long.

Des tests sur des Strings sont plutot longs.

Sinon, et bien je pense que tu vas gagner en ajoutant des indexs dans la mesure où tes colonnes du where seront des dates ou des entiers, sinon, ça ne sera pas aussi efficace je pense..

Maximilian : merci pour les précisions

A+

**Maximil ian** · 31/08/2006, 10h47

Envoyé par shkyo

Pour reprendre mon exemple en version SQL, les requêtes auront très souvent cette tête :

Code :

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

SELECT app_A,app_B,app_C FROM pressions WHERE dates='20060830'

ou encore :

Code :

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

SELECT app_A,app_B,app_C FROM pressions WHERE dates BETWEEN '20060825' and '20060830'

Qu'en pensez-vous ??

Fais un test avec et sans index sur la colonne dates...

Au passage de quel type est cette colonne ? [ edit : grillé par KiLVaiDeN sur ce coup-là

]

**shkyo** · 31/08/2006, 11h42

Envoyé par Maximilian

Fais un test avec et sans index sur la colonne dates...

Au passage de quel type est cette colonne ? [ edit : grillé par KiLVaiDeN sur ce coup-là

]

La BD n'existe pas encore, je récléchis avant pour partir d'un bon pied, donc pour les tests live...

La plupart de mes colonnes, à part l'ID qui servira de clef primaire (de type MEDIUMINT UNSIGNED AUTO_INCREMENT) et une colonne au format DATE, vont être principalement des FLOAT(x,1) UNSIGNED, ou le 'x' fera entre 2 et 4 suivant les appareils, plus quelques TINYINT UNSIGNED et MEDIUMINT UNSIGNED.

**shkyo** · 31/08/2006, 14h34

Petite question, qui solutionnerait mes interrogations sur les index, actuellement j'utilise la version 5.0.22 de MySQL, est-ce que le système de partionnement en toujours en béta ou non ??

Car si c'est devenu opérationnel, il me suffira de partionner mes tables mois par mois, et comme cela je ne pense pas avoir de soucis de perfs, non ??