Optimiser sa base de données: dupliquer intelligeamment l'information.

Compter prend du temps, de l'argent et de l'énergie.

Nous avons vu dans le chapitre précédent que l'information prend du volume et que le volume d'information diminuait les performances de mysql. Pour autant, toute règle a une exception.

Le format de table le plus utilisé (et le plus recommandé) de mysql est le format innoDB. Sans rentrer dans les détails techniques, ce format est utilisé pour deux raisons:

• Lors d'une écriture, il verrouille uniquement (et temporairement) les enregistrements dont il a besoin. Myisam, par exemple, verouille des tables entière.
• Il est un un des seuls formats sérieux à pouvoir gérer les transactions.

Pour autant, innoDB à des désavantages. Le principal étant de mettre du temps compter ou d'obtenir des données agrégées.
Par exemple, si je veux pouvoir afficher la liste de mes billets et le nombre de votes, je serais obligé de faire 2 requêtes et d'assembler ensuite les résultats dans mon script:

SELECT * from billets where id_utilisateur='3128';

Temps d'exécution: 0.01 secondes.

Select id_billet, count(id_vote) as nbVotes from votes where id_utilisateur='3128' group by id_billet use index index_id_utilisateur;

 Temps d'exécution: 0.3 secondes.
Autrement dit, j'ai multiplié mon temps de recherche d'information par 30, sans compter le temps que le script mettra à dé-multiplexer l'information.

De là, j'ai deux solutions: soit ma base est une base d'écriture (type boursorama) et cela ne me dérange pas plus que cela, soit j'ai une base de lecture et c'est un problème.
La solution simple serait peut être tout simplement de ne pas afficher les votes, de ne pas classer par vote et de ne pas paginer par votes. Mais un système judicieux permet de passer ce problème sans trop de difficultés: il suffit de rajouter un champ "nbVotes" dans la table "billet".

Ainsi, en cherchant les billets, j'aurais tout de suite leur nombre de votes.
Ce système me fait gagner pas mal de performance, mais comporte deux désavantages:

• A chaque fois que je veut ajouter un vote, ceci me prend une requête d'ajout de vote ET une requête de mise a jour du nombre de votes de mon billet:

Update billets set nbVotes = nbVotes+1 where id_billet='215987';

•  De temps en temps (tous les mois ou toutes les semaines à 4 heures du matin) je devrais lancer un script mettant à jour ces données.

Ces actions sont très recommandées si votre projet prend de l'ampleur et du trafic. Prendre le double de temps en écriture pour gagner 30 fois plus de temps à chaque lecture me parait un bon calcul pour se permettre ces contraintes.

De même, imaginez que non seulement je veuilles afficher le nombre de votes, mais aussi la dernière date où mon billet à été modifié (ajout d'un vote, ajout d'un message ou message re-publié), cela donnerais:

Select max(date_changement) as derniere_date_changement
from (
   (select date_publication as date_changement from billets where id_billet='215987')
   union
   (select max(date) as date_changement from votes where id_billet='215987')
   union
   (select max(date) as date_changement from commentaires where id_billet='215987')
) as b;

 Imaginez la performance de mysql sur ce genre de requête !

Cela serait bien plus simple et rapide si l'information était déjà "digérée" et présente à l'intérieur de mon enregistrement "billet" sous le champ "derniere_date_changement".

Conclusion:
Prendre un peu de volume et perdre du temps sur une écriture n'est jamais un mauvais calcul si cela fait gagner du temps sur des milliers de lectures. Aussi la duplication d'informations pour faciliter la récupération des données agrégées est en soi un très bon principe.

Capacité de réduction du temps de réponse: au moins 90% sur les requêtes retournant des données agrégées.