Discussion :

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.