L'accès a mes données SQL rame !

Version imprimable

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:

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

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:

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

03/07/2015, 19h42
Christophe

Pour commencer, ta base est-elle indexée ?
03/07/2015, 19h46
slash031

Le champ id est en PRIMARY KEY
J'ai rajouté un index sur le champ "country" pour voir, mais ça ne change rien
03/07/2015, 21h16
Christophe

Essayes d'ajouter des index sur assoc et status
03/07/2015, 21h17
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.
04/07/2015, 01h48
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!
04/07/2015, 22h12
Thes32

Citation:

Envoyé par Tsilefy

MySQL est un langage de programmation

Depuis quand ?
06/07/2015, 16h33
Tsilefy

Citation:

Envoyé par Thes32

Depuis quand ?

Je voulais dire SQL :-)
07/07/2015, 02h27
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
07/07/2015, 11h10
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)
07/07/2015, 18h22
slash031

Citation:

Envoyé par 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)

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 :/
08/07/2015, 01h13
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:

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
08/07/2015, 15h56
slash031
Citation:

Envoyé par 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:

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:

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
08/07/2015, 16h36
Thes32

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

Citation:

Envoyé par Celira

Et sinon pour les indexes, personnellement, je mettrais un index sur assoc, status et country (un seul index comprenant les 3 colonnes)
08/07/2015, 21h19
slash031

Citation:

Envoyé par Thes32

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)
08/07/2015, 23h52
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:

SHOW CREATE TABLE places;

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:

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:

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

16/07/2015, 15h12
slash031

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