Indexes de la bdd

bonjour,

Suite à l'amélioration globale des connexions grâce au travail d'Yvan j'ai pu constater que certaines optimisations étaient encore réalisables sans impact notable sur le code écrit.
Il s'agit de la création d'indexes dans la base de données.
Cela peut avoir un gros impact sur les performances puisque par nature certaines tables de Noethys vont devenir volumineuses (prestations, consommations....) et sont souvent accédées.
Le principe est assez simple il suffit de créer les indexes sur les colonnes qui sont utilisées lors des requetes.

Un exemple :
la table des consommations a pour l'instant un seul index qui correspond a sa clé primaire (Colonne IDConso) or il y a rarement besoin d'aller chercher une consommation par son IDConso mais très souvent par IDactivite et date, par exemple pour connaitre tous les enfants étant inscrit a l'activité 1 entre le 01/09/2015 et le 15/09/2015 la requete composée par noethys ressemble a cela (bien sur les deux __ sont de trop mais sinon le forum refuse mon message...) :
Chez moi cela lui fait parcourir les 50000 lignes de la table des consos soit 0.100s. (Noethys n'est pas installé depuis très longtemps mais chez vous il y a peut etre beaucoup plus de lignes)

Si je place un index sur les colonne date et IDactivite qui servent ici au filtre j'obtiens les résultats en 0.020s soit 5 fois plus rapide normal puisqu'il a juste a parcourir les lignes de son index
bien sur il ne s'agit pas de créer des indexes sur toutes les colonnes (cela serait fastidieux et contre-productif) mais plutôt d'identifier les requêtes coûteuses qui sont souvent exécutées
La mise en place des indexes permet surtout de limiter le temps d'execution des requetes pour lequel le ratio "lignes retournées"/"nb ligne de la table" est faible puisque si toute la table est renvoyée l'index ne sert a rien.

Cette optimisation sera très visible pour ceux pour qui le serveur mysql est sur un serveur peu puissant / mal dimensionné / avec un disque lent (ordi portable) et beaucoup moins sur les machines rapides et/ou puissantes (exemple le serveur dédié d'Yvan) qui vont tout mettre en cache ou lire les données des tables à la vitesse de l'éclair (avec un SSD par exemple)

PS : Attention si vous executer plusieurs fois la même requete pour effectuer des test vos résultats seront faussés puisque mysql a un certin nombre de caches qu'il utilise pour stocker les dernieres requetes et leurs résultats

Je vais prendre un peu de temps pour indiquer dans ce fil les indexes que j'ai déjà pu créer sur ma base et les écrans dans lesquels ils sont utilisés.

cdlt

Bonjour,
Toi il ne faut pas qu'on te perde !
Merci pour ta précieuse contribution.

ne t'en fait pas c'est purement egoiste moins ma femme passe de temps sur Noethys et plus elle a du temps pour moi

Bonjour,

Je viens également de voir que toutes les dates sont stockées sous la forme de chaîne de caractères.
Idéalement, elles devraient être stockées avec le type "datetime", voir "date" uniquement s'il n'y a pas d'heure à stocker.
La construction d'un index sur ces champs et la récupération des informations serait beaucoup plus optimisée.

Par contre, je n'ai pas encore regardé, mais l'impact sur le code n'est peut-être pas anodin...

Olivier

Bonjour Olivier,

cela a également été une de mes idées mais par contre les résultats ne confirment pas trop j'ai le même temps dans les 2 cas...
Au niveau code je pense que cela ne change rien pour les dates seules puisque Ivan utilise le format standard AAAA-MM-JJ pour les dates et heures je n'ai pas regardé.
Olivier si tu as une base un peu conséquente peux-tu regarder quels temps tu obtiens ?
(tu peux créer une nouvelle colonne date dans la table puis effectuer un simple "update table set nouvelle_date = ancienne_date")

Bonsoir,

1. Pour le champ DATE : aucune idée sur le fait qu'il puisse y avoir un impact ou non...

2. Pour les indexes : J'ai déjà créé des indexes (il y a peu). Il est possible de les consulter dans le fichier DATA_Tables.py. Tout à la fin du fichier il y a le dictionnaire DB_INDEX. J'ai hésité à en rajouter trop car il paraît qu'il ne faut pas trop en abuser. Mais je me demande jusqu'où ?

En tout cas, il est facile d'en rajouter, il suffit de les insérer dans le fichier DATA_Tables.py. Exemple :
Puis de faire le code :

Il créera uniquement les indexes manquants.

Ivan

goutatou écrit: si tu as une base un peu conséquente

Malheureusement, je n'ai qu'à peine 10.000 lignes de conso pour l'instant...

Sinon, c'est clair que le passage de varchar en date ne va pas révolutionner les temps d'accès aux données.
C'était plus une réflexion performances globales, mais je ne l'ai pas précisé, désolé.
Ca peut (je dis bien "peut" ), entre autres, permettre de soulager un peu le processeur qui exécute la requête, ou encore d'utiliser moins de mémoire (que ce soit de la mémoire vive ou de l'espace disque, il me semble qu'une date en varchar(10) utilise 2,5 à 3 fois plus de place que la même date en date).
Tout ça peut devenir intéressant par exemple, pour ceux qui stockent la base sur un NAS équipé d'un MySQL ou d'un MariaDB.

De toute façon, les index restent la principale piste à privilégier dans l'amélioration des performances.

Olivier