Je travaille actuellement sur une table MySQL qui va contenir près de 7 millions de tuples (avec une quinzaine de colonnes chacun bien sûr, ce ne serait pas drôle sinon !), il se trouve qu’elle en contient déjà près de 3 millions, et que je suis obligé de faire une vingtaine de requêtes différentes dans cette table pour chaque page et que, par conséquent, le site qui l’utilise plante à fond. La mémoire utilisable par MySQL est dépassée. Alors certes, on peut modifier la taille de la mémoire utilisable par MySQL (et il faudrait aussi sûrement modifier la durée maximum d’exécution d’un script PHP), mais le but c’est quand même de charger des pages en moins de 30 secondes. J’ai donc cherché des réponses sur le Web et auprès des développeurs de ma boîte. Voici donc quelques points que j’ai pu retenir : !!!Structure de la table Il vaut mieux éviter, sauf si c’est nécessaire, l’utilisation de champ »NULL » par défaut. En effet, comme on peut le voir sur [cet article d'Apprendre PHP|http://www.apprendre-php.com/tutoriels/tutoriel-26-mysql-introduction-a-l-optimisation.html] »NULL » est une valeur spéciale qui nécessite un traitement spécial, MySQL ne peut pas le tester avec un égal et ne peut le comparer à la chaine vide ou à 0, il est obligé d’utiliser @@IS@@ ou le symbole équivalent @@<=>@@. »NULL » par défaut est d’autant plus à bannir pour les index. Utiliser des colonnes avec le meilleur type et la meilleure taille possible. Un prénom n’a par exemple par besoin d’être en VARCHAR(255), un VARCHAR(20) suffit plus que largement. Une bonne technique pour optimiser ses tables est d’utiliser @@PROCEDURE ANALYSE()@@. On créé ses tables au feeling (je parle juste de la structure, vous ne couperez pas à la normalisation tout ça, héhé), on les remplie avec un contenu à peu près définitif est on utilise une requête du style : ///[mysql] SELECT id, titre, message FROM blog PROCEDURE ANALYSE() /// MySQL va nous répondre un joli petit array avec la taille max et min de chaque colonne id, titre et message, quelques autres informations intéressantes et surtout une proposition de type(taille) optimisée à votre table. Encore une fois, c’est [l'article d'Apprendre PHP|http://www.apprendre-php.com/tutoriels/tutoriel-26-mysql-introduction-a-l-optimisation.html] qui en montre un exemple vraiment parlant. !!!SELECT * Si l’on effectue une recherche sur une colonne et que celle-ci change assez rarement (ou n’est modifié que par un administrateur), il peut être très avantageux de créer un index ou un index unique (si le contenu de cette colonne est différente pour chaque tuple) dessus. Il suffit de le faire en ligne de commande, ou directement dans PhpMyAdmin. Cela peut mettre un petit peu de temps si la table est grande, ce qui explique qu’il vaut mieux éviter de changer trop souvent la table par la suite puisqu’il faut mettre à jour l’index à chaque insert, update, delete. Si j’ai bien compris MySQL se charge alors de créer une sorte de table virtuelle avec cette colonne ce qui accélère grandement la recherche sur cette dernière. On peut créer autant d’index que l’on souhaite, mais s’il y en a trop l’avantage peut être restreint. (o alors il faut utiliser des USE INDEX (nom_index) dans vos requêtes, mais pour moi ça a plutôt ralenti mes requêtes) * Bien entendu, l’utilisation du joker (*) est à bannir. (en l’utilisant on effectue 2 requêtes au lieu d’une : chercher tous les champs qui correspondent au joker (ce que l’on aurait du faire à la main), puis sélectionner les bonne colonnes selon les bonne lignes) De même, si c’est possible comme pour un forum ou un système de commentaires, au lieu d’utilisation un count(champ1), il vaut mieux mettre le nombre de tuples en dur dans la base de données. * Lors de jointure, il est important que les critères de jointure soient de même type pour que MySQL n’est pas à faire de conversion. L’utilisation de jointures avec JOIN est a priori plus rapide que celles (à l’ancienne) dans le WHERE. Et de même, il est plus intéressant de faire un LEFT JOIN qu’un JOIN (quand le résultat convient avec un LEFT JOIN bien sûr) * On peut faire précéder sa requête SQL par EXPLAIN pour savoir comment MySQL traite notre SELECT. MySQL nous renvoie alors un tableau avec tout un tas d’information. Il suffit de lire la [doc MySQL de EXPLAIN|http://dev.mysql.com/doc/refman/5.0/fr/explain.html] pour utiliser cette commande. !!!INSERT Si l’on a a effectuer plusieurs insertions à la suite, il est plus avantageux de faire un seul INSERT que plusieurs à la suite. Bref, au lieu de faire : /// for($i=0;$i<100;$i++) { $qry ='INSERT INTO ma_table (numero) VALUES ("'.$i.'")'; $insert = mysql_query($qry); if (!$insert) echo 'Erreur lors de l\'insertion : '.mysql_error().'
‘; } /// Faites plutôt : /// $values = array(); for($i=0;$i<100;$i++) { $values[] = $i; } $qry ='INSERT INTO ma_table (numero) VALUES ("'.implode('"), ("', $values).'")'; $insert = mysql_query($qry); if (!$insert) echo 'Erreur lors de l\'insertion : '.mysql_error().'
‘; /// !!!Partitionnement L’autre solution consiste à partitionner sa table. Je vous laisse lire ce [très bon tutoriel sur Developpez.com|http://krierjon.developpez.com/mysql/partitionnement/]. C’est au final ce que j’ai choisi et le résultat est plutôt positif : j’arrive à accéder à toutes mes pages en moins de 30 secondes, et les pages qui n’avaient vraiment pas besoin de toutes les données sont vraiment rapide à charger. !!!Conclusion Vous pouvez lire ce long article très intéressant et très complet de PHP Facile à ce sujet : [Optimisation de MySQL|http://www.lephpfacile.com/manuel-mysql/mysql-optimization.php] Et bien sur l’article dont j’ai déjà parlé sur Apprendre PHP : [Introduction à l'optimisation|http://www.apprendre-php.com/tutoriels/tutoriel-26-mysql-introduction-a-l-optimisation.html]. Aucun de ces 2 articles ne parlent de partitionnement ce qui est assez étonnant. Bon, tout cela étant dit, la meilleure technique consiste assurément à créer un système de cache. Le premier utilisateur allant sur la page se tape les requêtes SQL et la génération du cache, mais toutes les prochaines visites (pour tout le monde) seront très rapide puisqu’elles iront juste lire un fichier HTML. Bien sûr, vous pouvez aussi générer automatiquement tout votre cache en visitant chaque page avec @@file_get_content()@@. D’autre part, si vos données changent assez souvent, vous pouvez créer un petit robot qui se charge de supprimer vos fichiers de cache à intervalle régulier, mais dans ce cas là, il vaut mieux regénérer le cache à chaque modification de la BDD.