Discussion :

[slash031]

Bonjour

Je viens vers vous car j'ai un probleme c'est temps ci. J'ai une table SQL trés grosse et l'accés a mes données prennent 20 secondes ...

La requete en question :

Code :

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

$req2=mysql_query("SELECT id,country,place,gps,type,longueur,niveau,duree,vu FROM places WHERE assoc='0' AND status<5 AND country IN ('FR','ES','IT','GB') ORDER BY vu DESC LIMIT 100");

Ma table "places" fait 568 214 enregistrement soit 146 Mo


Voici la structure de ma table :

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
19
20
21
22
23
1	id	        int(10)			                        Non 	Aucune	AUTO_INCREMENT
2	country	varchar(5)	        utf8_general_ci		Non 	Aucune	
3	place	        varchar(250)	utf8_general_ci		Non 	Aucune	
4	gps	        varchar(250)	utf8_general_ci		Oui 	NULL		
5	localisor	int(10)			                        Non 	0		
6	type	        varchar(250)	utf8_general_ci		Oui 	NULL		
7	vu	        int(10)			                        Non 	0		
8	date_add	datetime			                        Non 	Aucune	
9	i_custom	text	                utf8_general_ci		Oui 	NULL		
10	i_wiki 	text	                utf8_general_ci		Oui 	NULL		
11	i_routard	text	                utf8_general_ci		Oui 	NULL		
12	i_fute	        text	                utf8_general_ci		Oui 	NULL		
13	population	int(10)			                        Oui 	NULL		
14	surface	int(10)			                        Oui 	NULL		
15	acces	        varchar(250)	utf8_general_ci		Oui 	NULL	
16	acces1	text	                utf8_general_ci		Oui 	NULL		
17	duree	        varchar(250)	utf8_general_ci		Oui 	NULL	
18	longueur	varchar(250)	utf8_general_ci		Oui 	NULL	
19	niveau	varchar(250)	utf8_general_ci		Oui 	NULL		
20	circuit	longtext	        utf8_general_ci		Oui 	NULL		
21	contributor	int(10)			                        Oui 	NULL		
22	assoc	        int(10)			                        Non 	0		
23	status	int(1)			                                Non 	0

Voici un exemple d'enregistrement :

Code :

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

579070 	ES 	Requena Casa Medina Requena 	39.484306,-1.098479 	0 	vtt 	0 	2015-07-03 18:07:59 	larga y dura	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2h48 	43,31 	3 	NULL	NULL	0 	0

Si quelqu'un sait ce qui fait ramer et comment y remédier .....
Merci par avance

[chrtophe]

Pour commencer, ta base est-elle indexée ?

[slash031]

Le champ id est en PRIMARY KEY
J'ai rajouté un index sur le champ "country" pour voir, mais ça ne change rien

[chrtophe]

Essayes d'ajouter des index sur assoc et status

[Thes32]

Salut,

Tu dois commencer par créer un ou plusieurs indexes sur ta table et ensuite ajuster ta requête en fonction de l'indexe.

[Tsilefy]

MySQL est un langage de programmation qu'il faut apprendre au même niveau que PHP (voire plus, puisque c'est le facteur principal de ralentissement des sites).

Lis le tutorial que tout développeur doit lire: Sers-toi de l'index, Luke!

C'est la réponse à toutes tes questions!

[Thes32]

MySQL est un langage de programmation

Depuis quand ?

[Tsilefy]

Depuis quand ?

Je voulais dire SQL :-)

[slash031]

J'ai testé avec des index, ça change rien ....
Par contre j'ai remarqué un truc : depuis mon site cette requete met 20/30 secondes a s'exécuter alors que depuis phpMyAdmin ça met 0.60sec

[Celira]

Est-ce vraiment la requête qui met 20 secondes ? ou est-ce l'intégralité du script ?

Et sinon pour les indexes, personnellement, je mettrais un index sur assoc, status et country (un seul index comprenant les 3 colonnes)

[slash031]

Est-ce vraiment la requête qui met 20 secondes ? ou est-ce l'intégralité du script ?

Et sinon pour les indexes, personnellement, je mettrais un index sur assoc, status et country (un seul index comprenant les 3 colonnes)

Salut. Merci pour ta réponse.
J'ai testé de mettre un index comprenant les 3 colonnes, ça ne change rien à la vitesse d'exécution ...

Et oui c'est vraiment la requete qui met 20 secondes... J'ai utilisé la fonction PHP getmicrotime() (une fonction trouvable sur le net) que j'ai mise juste avant et juste aprés la requete mysql_query() et c'est ce résultat qui me met 20 secondes :/

[Tsilefy]

À moins que phpMyAdmin ne cache les résultats, c'est très étrange. Est-ce que tu as un accès console à ta machine? Si oui, fais ta requête à partir de la ligne de commande pour voir.

Fais un EXPLAIN sur ta requête et regarde quels index (et dans quel ordre) sont utilisés.

Code :

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

EXPLAIN SELECT id,country,place,gps,type,longueur,niveau,duree,vu FROM places WHERE assoc='0' AND status<5 AND country IN ('FR','ES','IT','GB') ORDER BY vu DESC LIMIT 100

[slash031]

À moins que phpMyAdmin ne cache les résultats, c'est très étrange. Est-ce que tu as un accès console à ta machine? Si oui, fais ta requête à partir de la ligne de commande pour voir.

Fais un EXPLAIN sur ta requête et regarde quels index (et dans quel ordre) sont utilisés.

Code :

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

EXPLAIN SELECT id,country,place,gps,type,longueur,niveau,duree,vu FROM places WHERE assoc='0' AND status<5 AND country IN ('FR','ES','IT','GB') ORDER BY vu DESC LIMIT 100

J'ai fais la requete depuis PhpMyAdmin voici la réponse :

Code :

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

1
2
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	places 	range 	country 	country 	25 	NULL	329844 	Using where; Using filesort

[Thes32]

Vu le résultat de la commande EXPLAIN, tu n'as pas du tout suivi le conseil :

Et sinon pour les indexes, personnellement, je mettrais un index sur assoc, status et country (un seul index comprenant les 3 colonnes)

[slash031]

Vu le résultat de la commande EXPLAIN, tu n'as pas du tout suivi le conseil :

Quand j'ai fais cette requete il y avait (d'ailleurs il y a toujours) un index englobant ces 3 champs + un index sur id (UNIQUE)

[Tsilefy]

Peux-tu poster la partie de ton script qui traite de la requête, stp?

Poste également la structure complète de la table:

Code :

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

SHOW CREATE TABLE places;

[slash031]

Salut

Pour la requete c'est inutile : j'ai mis le "chronomètre" autour de la fonction mysql_query() uniquement. Mais bon je la poste quand meme :

Code :

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

1
2
3
while ($data2=mysql_fetch_array($req2)) {
	array_push($prox,strlen(round($data2['distanceee'])).'||'.round($data2['distanceee']).'||'.$data2['country'].'||'.$data2['place'].'||'.$data2['type'].'||'.$data2['longueur'].'||'.$data2['niveau'].'||'.$data2['duree'].'||'.$data2['id']);
}

SHOW CREATE TABLE places; donne :

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
19
20
21
22
23
24
25
26
27
28
 
CREATE TABLE `places` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `country` varchar(5) NOT NULL,
 `place` varchar(250) NOT NULL,
 `gps` varchar(250) DEFAULT NULL,
 `localisor` int(10) NOT NULL DEFAULT '0',
 `type` varchar(250) DEFAULT NULL,
 `vu` int(10) NOT NULL DEFAULT '0',
 `date_add` datetime NOT NULL,
 `i_custom` text,
 `i_wiki` text,
 `i_routard` text,
 `i_fute` text,
 `population` int(10) DEFAULT NULL,
 `surface` int(10) DEFAULT NULL,
 `acces` varchar(250) DEFAULT NULL,
 `acces1` text,
 `duree` varchar(250) DEFAULT NULL,
 `longueur` varchar(250) DEFAULT NULL,
 `niveau` varchar(250) DEFAULT NULL,
 `circuit` longtext,
 `contributor` int(10) DEFAULT NULL,
 `assoc` int(10) NOT NULL DEFAULT '0',
 `status` int(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `country` (`country`,`assoc`,`status`)
) ENGINE=MyISAM AUTO_INCREMENT=670770 DEFAULT CHARSET=utf8

[slash031]

Est ce qu'il existe des outils de testage d'un site ? Qui détecteraient la source d'une lenteur par exemple.