[spip-dev] [SQL] spip_articles.statut='publie'

Didier_Lebrun · Mars 23, 2002, 3:14

MySQL n'utilise qu'un seul index par table et donne des performances assez médiocres avec les index MULTIPLE. Par contre, les performances sont excellentes avec les index UNIQUE. Le meilleur moyen d'optimiser les requêtes compte-tenu des particularités d'utilisation des index par MySQL est de créer plusieurs index UNIQUE commençant par les différents champs que l'on souhaite indexer. Exemple:

CREATE TABLE xyz (
         champ1 INT UNSIGNED NOT NULL DEFAULT 0 PRIMARY KEY,
         champ2 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
         champ3 VARCHAR(50) NOT NULL DEFAULT '?',
         champ4 TEXT),
         UNIQUE(champ1, champ2, champ3),
         UNIQUE(champ2, champ3, champ1),
         UNIQUE(champ3, champ1, champ2);

Dès lors, toutes les requêtes dont les clauses font appel à champ1, champ2 et/ou champ3 utiliseront les index de manière optimale, quel que soit l'ordre d'apparition des champs dans les clauses. Le seul inconvénient de cette méthode est que les fichiers d'index prennent pas mal de place sur le disque du fait de leur multiplication.

J'utilise personnellement cette méthode avec des bases de données économétriques de plusieurs millions d'enregistrements comportant beaucoup de champs indexables (année, mois, pays, région, nomenclatures, etc...) et j'obtiens les réponses en moins de 5 secondes dans la quasi-totalité des cas alors que cela mettrait 10 à 15 minutes sur le même serveur si les index n'étaient pas optimisés (pour autant que le serveur ne timeoute pas avant). J'ai mesuré des écarts allant jusqu'à 1/200 entre une indexation optimisée et une indexation médiocre.

Il vaut mieux évidemment limiter le type de champ au minimum de bits nécessaire et utiliser les types TINYINT, SMALLINT, MEDIUMINT, INT partout où cela est possible. Il est également préférable de spécifier UNSIGNED partout où on sait que les valeurs ne peuvent pas être négatives.

Dans le cas présent, il vaudrait mieux à mon avis:
         - créer une table spip_statuts: create table spip_statuts (id_statut tinyint
unsigned not null defaut 0 primary key, statut varchar(10) not null default '?', unique(id_statut, statut), unique(statut, id_statut));
         - transformer le champ spip_articles.statut en champ tinyint pour manipuler des petits entiers plutôt que des varchar(10)
         - limiter le champ spip_mots.titre à 255 caractères et le transformer en varchar(255) pour pouvoir l'indexer de manière optimale
         - indexer les champs de spip_mots dans des index UNIQUE(id_mot, type, titre, maj), UNIQUE(type, titre, maj, id_mot), UNIQUE(titre, maj, id_mot, type), UNIQUE(maj, id_mot, type, titre)
         - transformer les champs BIGINT(21) en INT(10) UNSIGNED dans la mesure où 4.294.967.295 mots devrait largement suffire; même chose pour les articles et autres tables

[...]

63438612493668b99328 · Mars 23, 2002, 3:57

Didier Lebrun wrote:

- créer une table spip_statuts: create table spip_statuts (id_statut tinyint
unsigned not null defaut 0 primary key, statut varchar(10) not null default '?', unique(id_statut, statut), unique(statut, id_statut));

Ce n'est pas vraiment le facteur limitant. Comparer 1983 chaînes (taille
du résultat de Stéphane) de 10 caractères maxi ne prend pas 4 minutes. Par
contre, aller chercher 1983 chaînes de 10 caractères dans un énorme fichier
de 180 000 enregistrements de plusieurs kilo-octets chacun, ça peut prendre
bcp de temps si le fichier n'est pas en cache. D'où mes questions sur la
mémoire.

Pour en avoir le coeur net, il faudrait créer une table spip_articles_test
identique à la première, sauf les champs texte et chapo laissés vides. Vérifier
que cette table est bcp plus petite, et y lancer les deux requêtes en question
pour voir si ça améliore bcp les choses.

Si c'est bien là le problème, je vois deux solutions :

- rajouter un ou deux gigas de mémoire à la machine. Rapide mais sans garantie
de résultat.

- déporter les champs chapo et texte de spip_articles dans une table
séparée, qui ne sera accédée qu'une fois toutes les conditions vérifiées
et le tri effectué. Ca implique un certain nombre de modifs dans SPIP

Au fait, dans l'espace public, le cache risque de poser problème : 180 000
articles cachés dans 16 répertoires différents => plus de 10 000 fichiers
par répertoire => éventuelle baisse des performances (dépend du système de
fichiers, je ne saurais en dire plus).

Amicalement

Antoine.

Didier_Lebrun · Mars 23, 2002, 7:04

Le problème est effectivement que spip_articles.statut='publie' n'est pas recherché parmi les 1983 résultats mais dans l'ensemble de la table articles, et ce sans utiliser les index, donc le cache. Le problème existe également pour spip_mots.titre='Economie', mais la table spip_mots est probablement plus petite, ce qui pénalise moins les performances.

Quand Stéphane dit que MySQL "se tamponne" du fait que statut soit indexé, c'est parce que MySQL n'utilise qu'UN SEUL index par table. Il utilise en l'occurrence l'index PRI id_article et pas l'index MUL de statut.

Une solution pour améliorer sensiblement les performances sans rien changer serait d'ajouter simplement un index unique comportant à la fois le champ id_article et le champ spip_articles.statut:
ALTER TABLE spip_articles
ADD UNIQUE(id_article, statut);
Ca ne coûte pas grand chose à essayer

Le fait de transformer le champ statut de varchar(10) en tinyint améliorerait encore les choses, mais c'est mineur en comparaison de l'utilisation/non utilisation de l'index et cela impliquerait quelques changements.

d7da21bcd596933ad5dc · Mars 25, 2002, 10:49

le Sat, 23 Mar 2002 20:04:07 +0100
Didier Lebrun <dl@quartier-rural.org> écrivait :

Quand Stéphane dit que MySQL "se tamponne" du fait que statut soit
indexé, c'est parce que MySQL n'utilise qu'UN SEUL index par table. Il
utilise en l'occurrence l'index PRI id_article et pas l'index MUL de
statut.

NON !
MySQL n'utilise pas qu'un seul index par table ... totalement faux.
Fais un EXPLAIN <ma super requête que j'ai> pour constater qu'il
utilise plusieurs index par table ... juste un par colonne

ADD UNIQUE(id_article, statut);
Ca ne coûte pas grand chose à essayer

Ca n'a pas d'intêret que d'empécher des doublons de couples
(id_article,statut) et comme id_article est unique ...

Le fait de transformer le champ statut de varchar(10) en tinyint
améliorerait encore les choses, mais c'est mineur en comparaison de
l'utilisation/non utilisation de l'index et cela impliquerait quelques
changements.

Ca, c'est clair en plus, en terme de place sur disque et de
fragmentation des tables, c'est toujours mieux d'utiliser des champs
de taille fixe.

Didier_Lebrun · Mars 25, 2002, 3:05

le Sat, 23 Mar 2002 20:04:07 +0100
Didier Lebrun <dl@quartier-rural.org> écrivait :
> Quand Stéphane dit que MySQL "se tamponne" du fait que statut soit
> indexé, c'est parce que MySQL n'utilise qu'UN SEUL index par table. Il
> utilise en l'occurrence l'index PRI id_article et pas l'index MUL de
> statut.

NON !
MySQL n'utilise pas qu'un seul index par table ... totalement faux.
Fais un EXPLAIN <ma super requête que j'ai> pour constater qu'il
utilise plusieurs index par table ... juste un par colonne

N'ayant pas refait d'essais là-dessus depuis assez longtemps, j'ai fait quelques EXPLAIN pour le cas où quelque chose m'aurait échappé dans les versions récentes de MySQL, mais je n'ai pas réussi à lui faire utiliser plusieurs index sur une même table dans une même requête !? Par ailleurs, la doc de MySQL (3.23.40) semble corroborer cela: "§13.4 Suppose you issue the following SELECT statement: mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows." La doc dit bien "the most restrictive index" et non "indexes" et "that index" et non "these indexes".

Si toutefois tu as un exemple qui contredit cela, fais le moi passer, je suis preneur.

> ADD UNIQUE(id_article, statut);
> Ca ne coûte pas grand chose à essayer

Ca n'a pas d'intêret que d'empécher des doublons de couples
(id_article,statut) et comme id_article est unique ...

L'intérêt est de:
1) regrouper les deux champs de conditions de spip_articles dans un même index
2) bénéficier du fait que l'utilisation d'index UNIQUE s'avère plus performante que les index MULTIPLE pour une raison dont j'ignore les subtilités techniques mais que j'ai pu observer expérimentalement

Mais, comme je le disais, le mieux est que Stéphane essaie sur sa grosse base d'articles et nous dise ce que ça donne.