Optimisation d'une requête ou structure de table

[jumphot]

Bonjour,

J'ai une question pour les experts

J'ai un script en php qui extrait les derniers documents transmis entre quelques centaines de personnes. La table qui contient les documents contient environ 850 000 lignes, ça fait un bon moment (depuis au moins 500 000 entrés dans la table en question) que l'affichage de la page php est lente. J'affiche uniquement les 50 derniers documents avec un système de pagination simple. J'ai inséré une fonction de débugguage qui m'a permis de détecter la cause du ralentissement, la requête de sélection dans cette table est très lente (environ 5 secondes). Même chose sous PHPMyAdmin

Voici la requête SQL:

Code :

1
2
3
4
5
6
7
8
SELECT documents.id, documents.name, documents.timestamp, 
  categories.name AS category_name, 
  categorys.id AS category_id 
FROM documents 
LEFT JOIN categories ON categories.id = documents.category 
WHERE documents.active=1 
ORDER BY documents.id DESC 
LIMIT 50

J'ai une clé primaire sur le champ documents.id et categories.id, un index sur le champ documents.category et documents.active.

Avez-vous une idée de comment optimiser ça ?

N.B, le système tourne sur un core i7 avec 6go RAM, la charge système est très basse.

Merci d'avance pour votre aide

[CinePhil]

Pour commencer, il y a une petite erreur dans ta requête :

Code :

1
2
categorys.id AS category_id 
LEFT JOIN categories

La table s'appelle categories ou categorys ?

Dans les deux cas, tu devrais nommer tes tables au singulier.

On peut voir la structure des tables et le résultat d'un explain de la requête ?

[jumphot]

Désolé pour l'erreur, c'est qu'il y a une partie du nom de mon site dans le nom des tables et je l'ai retiré avec des fonction search/replace de mon IDE, une erreur c'est glissé

La requête est bien :

Code :

1
2
3
4
5
6
7
8
SELECT documents.id, documents.name, documents.timestamp, 
  categories.name AS category_name, 
  categories.id AS category_id 
FROM documents 
LEFT JOIN categories ON categories.id = documents.category 
WHERE documents.active=1 
ORDER BY documents.id DESC 
LIMIT 50

Voici le résultat de EXPLAIN:

Code :

1
2
3
4
5
6
EXPLAIN SELECT documents.id, documents.name, documents.timestamp, categories.name AS category_name, categories.id AS category_id
FROM documents
LEFT JOIN categories ON categories.id = documents.category
WHERE documents.active =1
ORDER BY documents.id DESC 
LIMIT 50

Code :

1
2
3
id	select_type	TABLE	type	possible_keys	KEY	key_len	ref	rows	Extra
1	SIMPLE	documents	ref	active	active	2	const	866667	USING WHERE; USING filesort
1	SIMPLE	categories	eq_ref	PRIMARY	PRIMARY	1	dbname.documents.category	1

Merci encore

[CinePhil]

Et la structure des tables (résultat de SHOW CREATE TABLE de chaque table) ?

[jumphot]

Désolé pour le délai de réponse, il semble que nous ne soyons pas de le même fuseau horaire
Pour la table documents :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
TABLE	CREATE TABLE
documents	CREATE TABLE `documents` (
 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL DEFAULT '',
 `description` mediumtext,
 `search` varchar(255) NOT NULL,
 `link` varchar(255) DEFAULT NULL,
 `active` tinyint(1) DEFAULT '1',
 `category` tinyint(10) UNSIGNED DEFAULT NULL,
 `timestamp` bigint(25) NOT NULL,
 `hits` int(11) UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY  (`id`),
 KEY `active` (`active`),
 KEY `category` (`category`),
 KEY `timestamp` (`timestamp`),
 KEY `hits` (`hits`),
 FULLTEXT KEY `search` (`search`)
) ENGINE=MyISAM AUTO_INCREMENT=869047 DEFAULT CHARSET=latin1

Pour la table des catégories:

Code :

1
2
3
4
5
6
7
8
9
10
11
TABLE	CREATE TABLE
categories	CREATE TABLE `categories` (
 `id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT '0',
 `active` tinyint(3) UNSIGNED DEFAULT '1',
 `nb` int(10) UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY  (`id`),
 KEY `active` (`active`),
 KEY `parse_error` (`parse_error`),
 KEY `nb` (`nb`)
) ENGINE=MyISAM AUTO_INCREMENT=139 DEFAULT CHARSET=latin1

Merci!

[jumphot]

Je n'ai pas encore trouvé de solution, j'ai effectué les optimisations des tables et j'ai aussi regardé les suggestions PHPMyAdmin pour optimiser la structure. J'ai appliqué certaines recommandations tel que passer des champs INT(11) en MEDIUMINT(7) lorsque ça m’apparaissait approprié. Rien y fait, la requête écrite plus haut peut prendre plus de 10 secondes à être effectué la première fois. Ensuite mysql la met en cache et c'est rapide.

[SQLpro]

Déjà il faudrait peut être apprendre un minimum ce qu'est la modélisation des données, car votre clef étrangère category dans la table documents n'a pas le même type de données que celle dans la table des catégories.
C'est stupide car cela oblige à un transtypage et donc interdit l'utilisation des index.
Ceci aurait été vu par une modélisation correcte et la donc la mise en place de l'intégrité référentielle. Mais comme MySQL ne permet pas à la fois l'intégrité référentielle et l'indexation textuelle vous êtes baisé !

Ce n'est pas le moindre des maux de MySQL, qui, contrairement à une légende est un veau en matière de performances....

A lire sur le sujet : http://blog.developpez.com/sqlpro/p9...udre-aux-yeux/
Benchmark : http://blog.developpez.com/sqlpro/p9...lles-en-sql-1/
Indexation, comparaison des SGBDR : blog.developpez.com/sqlpro/p9816/langage-sql-norme/tout-sur-l-index/
Recherches textuelle comparatif MySQL / SQL server : http://blog.developpez.com/sqlpro/p9...ext-search-no/

A +

[CinePhil]

Une colonne ne devrait pas s'appeler "timestamp" car c'est un mot réservé du langage SQL.

Les index sur "active" sont inutiles car ce sont des booléens donc seulement 2 valeurs. Le SGBD n'utilisera jamais cet index, il aura plus vite fait de parcourir toute la table.

category.active devrait, si j'ai bien deviné son utilité, être un booléen, c'est à dire un TINYINT(1).

Comme a dit SQLPro, les colonnes faisant office de clé étrangère, ici document.category, doivent avoir exactement le même type que la colonne qu'elles référencent.

Que contiennent les colonnes search et hits ?

Dans votre requête, le ORDER BY est-il vraiement utile ? Qu'est-ce que ça apporte d'ordonner une liste de documents par son identifiant qui n'a aucune signification ?

[jumphot]

Merci à vous deux,

J'ai effectué vos recommandations,

-le champ timestamp devient added_timestamp

-les index sur les colonnes active sont supprimés.

-documents.active et categories.active sont maintenant tous les deux en tinyint(1) unsigned not null default 1

-les champs documents.category et categories.id ont dorénavant le même type tinyint(3)

-la colonne search contient un texte épuré tiré du titre du document. Cette colonne est utilisé avec un MATCH standard dans la section de recherche. Le système est efficace.

-La colonne hits contient le nombre de fois où le document à été vu. C'est utilisé à fin de statistiques pour celui qui publie ses documents.

-La clause ORDER BY documents.id DESC dans la requête c'est pour classer les résultats des plus récents au plus anciens puisqu'il s'agit de la clé primaire qui est en auto_increment

Le problème de performance c'est envolé au moment ou l'index des colonnes `active` ont étés retirés

Merci pour votre aide.

Si vous avez d'autres suggestions je suis preneur.

SQLpro, j'ai lu vos liens avec attention, je songeais il y a un bon moment à utiliser Progresql et je le ferai dans un futur proche pour mes prochain projets. Mais je n'envisage pas de changer mysql pour progresql sur les plateformes actives. Par exemple, j'ai un projet utilisant mysql pour en moyenne 300 connexions concurrentes avec plus de 500 000 requêtes par heure, moitié SELECT/UPDATE avec risque assez fort de collisions. Néanmoins le site fonctionne à merveille donc je lui toucherai le moins possible

Pour ce qui est des index et des systèmes de recherche, j'ai encore beaucoup de lecture à faire