Encore un joli problème SQL avec un gros site Spip qui rame trop.
Spip ajoute souvent à ses requêtes la condition :
AND articles.statut='publie'
et on ne peut pas la débrayer dans les squelettes.
Le problème, c'est qu'elle ralentit épouvantablement. Un exemple avec
le code produit par une boucle ARTICLES avec mots-clés (je simplifie
un peu la requête effectivement envoyée par Spip) :
Sans cette condition :
Non, ce n'est pas fichu ! La solution est de créer des index comportant plusieurs champs, ce qui permet de répondre à la condition "un seul index par table" tout en concernant plusieurs champs d'une même table. C'est ce que je proposais dans mon message du Sat, 23 Mar 2002 20:04:07 +0100. En l'occurence, il faudrait ajouter le champ 'statut' à la clef primaire puisqu'id_article y est déjà:
ALTER TABLE spip_articles DROP PRIMARY KEY,
ADD PRIMARY KEY (id_article,statut);
Dès lors qu'un index contient les 2 champs de conditions, MySQL l'utilise pour résoudre les 2 conditions. J'ai fait un EXPLAIN qui montre que ça fonctionne effectivement, sauf que mes tables SPIP ne contiennent pas assez d'enregistrements pour faire des tests de rapidité probants:
EXPLAIN SELECT count(articles.id_article) FROM spip_articles AS articles,spip_mots_articles,spip_mots WHERE articles.id_article=spip_mots_articles.id_article AND spip_mots_articles.id_mot=spip_mots.id_mot AND spip_mots.titre='Economie' AND articles.statut='publie'
Résultat de l'EXPLAIN:
0 table type possible_keys key key_len ref rows Extra
1 spip_mots_articles ALL id_mot,id_article 8
2 articles eq_ref PRIMARY PRIMARY 13 spip_mots_articles.id_article,const 1 where used; Using index
3 spip_mots ALL PRIMARY 5 where used
Cela ne résout cependant pas les problèmes des autres tables (spip_mots et spip_mots_articles), dont le type reste ALL, mais ça devrait déjà améliorer les temps de réponse. Pour que les jonctions entre les tables soient plus rapides, il serait possible de rajouter également un index multi-champs à spip_mots_articles:
ALTER TABLE spip_mots_articles ADD PRIMARY KEY (id_mot,id_article);
L'EXPLAIN montre alors une amélioration pour spip_mots_articles (type=ref):
0 table type possible_keys key key_len ref rows Extra
1 spip_mots ALL PRIMARY 5 where used
2 spip_mots_articles ref PRIMARY,id_mot,id_article PRIMARY 3 spip_mots.id_mot 1 Using index
3 articles eq_ref PRIMARY PRIMARY 13 spip_mots_articles.id_article,const 1 where used; Using index
Reste encore la table spip_mots, qui s'adapte plus difficilement à cause du champ 'titre' qui est déclaré en variable TEXT et qu'il faudrait d'abord passer en VARCHAR(255) pour pouvoir l'intégrer dans l'index primaire (NB: 255 caractères devrait être suffisant pour les mot-clefs):
ALTER TABLE spip_mots CHANGE titre titre VARCHAR(255) NOT NULL,
DROP PRIMARY KEY, ADD PRIMARY KEY (id_mot,titre)
Cette fois-ci, EXPLAIN montre que toutes les tables utilisent bien les index:
0 table type possible_keys key key_len ref rows Extra
1 spip_mots index PRIMARY PRIMARY 258 5 where used; Using index
2 spip_mots_articles ref PRIMARY,id_mot,id_article PRIMARY 3 spip_mots.id_mot 1 Using index
3 articles eq_ref PRIMARY PRIMARY 13 spip_mots_articles.id_article,const 1 where used; Using index
Pour gagner encore en rapidité, il est en outre possible de transformer les champs d'id des différentes tables qui sont en BIGINT (64 bits) -> MEDIUMINT UNSIGNED (24 bits), ce qui restera largement suffisant en terme de quantités d'articles et mots-clefs. Il est de toute façon probable que SPIP + MySQL aient du mal à gérer des quantités supérieures à 2^24 soit 17 millions d'enregistrement par table !
ALTER TABLE spip_articles CHANGE id_article id_article MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE spip_mots CHANGE id_mot id_mot MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE spip_mots_articles CHANGE id_article id_article MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL, CHANGE id_mot id_mot MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL;
Cela dit, il vaudrait mieux commencer par là, avant de créer/modifier les index pour que la création des index soit plus rapide.
NB: aucune de ces modifications n'interfère avec le code PHP de SPIP. C'est complètement indépendant et n'agit que sur la rapidité du serveur MySQL. Il faudra cependant se méfier lors des upgrades de SPIP, qui modifient parfois la structure des tables (inc_base.php3).
A+