Discussion :

[FMaz]

J'ai actuellement une requête plutôt complexe qui est trop lente.

Explication de l'utilité de la requête:
J'ai actuellement un jeu en ligne, dans lequel les joueurs ont un historiques des évènements, une sorte de liste de messages d'autres joueurs. Chaque message peut avoir un ou plusieurs envoyeurs et destinataire.

Le jeu offre la possibilité de renommer chaque connaissance (personnage). À la base, tous le monde est donc inconnu, ou inconnue si le sexe du personnage est féminin.


La requête:
Je l'ai laissée en PHP

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
 
$query = 'SELECT he.msg, he.date, he.id AS hid, he.type,'
					. ' pc.nom, ft.fromto, ft.persoid,'
					. ' p.sexe, p.imgurl, ft.`show`'
				. ' FROM ('
					. 'SELECT msgid, `show`'
					. ' FROM he_fromto'
					. ' WHERE `show`!=0'
						. ' AND persoid = ' . (int)$perso->getId()
					. ' ORDER BY `msgid` DESC'
					. ' LIMIT ' . (int)$from . ',' . (int)$nbr
				. ') as sq'
				. ' INNER JOIN he AS he ON (he.id=sq.msgid)'
				. ' INNER JOIN he_fromto AS ft ON ( ft.msgid = he.id )'
				. ' LEFT JOIN perso_connu AS pc'
					. ' ON (pc.persoid = ' . (int)$perso->getId()
						. ' AND pc.nomid = ft.persoid )'
				. ' LEFT JOIN perso AS p'
					. ' ON ( p.id = ft.persoid )'
				. ' ORDER BY he.`date` DESC, hid ASC, ft.fromto ASC , pc.nom ASC;';

Je possède 2 tables assez immenses:
he, qui contient tous les messages
he_fromto, qui contient tous les informations sur les envoyeurs ou les destinataire (champs `fromto`: from ou to). Cette table détermine aussi qui sont les joueurs qui ont effacés le message de leur historique des évènements (champ `show`). Lorsque tous les joueurs ont effacé le message, celui-ci est réellement supprimé.


But:
Je cherche donc à optimiser cette requête car elle prend parfois plusieurs secondes à être exécuté: beaucoup trop long !

En la regardant, j'ai réalisé que les jointures devaient logiquement faire des full table scan, et qu'il serait intéressant de les limiter:

INNER JOIN he AS he, il n'y a qu'un seul message par ligne de sq (message à afficher)
LEFT JOIN perso_connu AS pc, il n'y a qu'un seul personnage connu par ligne de ft (destinataire ou envoyeur)
LEFT JOIN perso AS p, il n'y a qu'un seul personnage par ligne de pc (perso connu)


Je demeure ouvert à toute suggestions qui ne demandent pas de chambouler trop de choses (vu que c'est la page principale du site, le coeur du moteur de jeu, et que plusieurs milliers de lignes reposent sur cette structure)




Merci à tous ceux qui ont pris la peine de me lire, j'espère avoir été plus clair que pénible à lire

[Maljuna Kris]

Saluton,
Je me permets de suggérer une syntaxe PHP alternative que je trouve plus lisible

Code php :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$query = sprintf('SELECT he.msg, he.date, he.id AS hid, he.type,
		         pc.nom, ft.fromto, ft.persoid,
		         p.sexe, p.imgurl, ft.`show`
                  FROM (SELECT msgid, `show`
                        FROM he_fromto
                        WHERE `show`!=0 AND persoid = %u
		        ORDER BY `msgid` DESC
		        LIMIT %u,%u) as sq
	          INNER JOIN he AS he ON he.id=sq.msgid
	          INNER JOIN he_fromto AS ft ON ft.msgid = he.id
	          LEFT JOIN perso_connu AS pc ON pc.persoid = %u AND pc.nomid = ft.persoid
	          LEFT JOIN perso AS p ON p.id = ft.persoid
	          ORDER BY he.`date` DESC, hid ASC, ft.fromto ASC , pc.nom ASC',
	          $perso->getId(),$from,$nbr,$perso->getId());

Sur le fonds de la problématique la sous-requête de la clause FROM doit plomber la performance globale.
Après, il faudrait s'assurer de la présence d'index sur toutes les colonnes qui participent aux conditions des jointures.

[FMaz]

Merci pour la lisibilité, c'est vrai que dans le cadre d'un sujet comme celui-ci, c'est plus clair. Personnellement je préfère demeurer aux normes de programmation de Zend; ca évite d'envoyer tout un tas d'espace et de TAB au serveur MySQL.

Pour la sous-requête FROM, je ne vois pas de meilleure façon de ressortir un nombre défini de messages. Les suggestions demeurent bienvenus.

En ce qui concerne les index, je n'y ai pas actuellement accès alors dès que je serais chez-moi, je re-répondrais pour clarifier ce point, qui a priori ressemble à ca:

he:
PRIMARY ( `id` )
INDEX (`date`)

he_fromto:
PRIMARY ( `persoid`, `show`, `msgid`, `type` )

perso:
PRIMARY ( `id` )

[CinePhil]

he_fromto:
PRIMARY ( `persoid`, `show`, `msgid`, `type` )

Si c'est comme ça, il faut indexer en plus et séparément les colonnes show, msgid et type qui ne sont pas automatiquement indexées de manière individuelle en dehors de la clé primaire.

[FMaz]

Show ne possède que 3 valeurs possible, je dois l'indexé quand même ?

[FMaz]

Voici les clés et les détails:

he_fromto:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
Nom	Type	Unique	Compressé	Champ	Cardinalité	Interclassement	
 
PRIMARY	BTREE	Oui	Non
					`persoid`0		A		
					`show`	0		A	
					`msgid`	0		A	
					`fromto`107224		A	
 
`show`	BTREE	Non	Non		`show`	3		A		
`fromto`BTREE	Non	Non		`fromto`2		A		
`msgid`	BTREE	Non	Non		`msgid`	26806		A

he:

Code :

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

1
2
3
4
 
Nom	Type	Unique	Compressé	Champ	Cardinalité	Interclassement	
PRIMARY	BTREE	Oui	Non		`id`	27595		A		
`date`	BTREE	Non	Non		`date`	13797		A

perso_connu

Code :

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

1
2
3
4
5
 
Nom	Type	Unique	Compressé	Champ	Cardinalité	Interclassement	
PRIMARY	BTREE	Oui	Non		`id`	2265		A		
`persoid`BTREE	Non	Non		`persoid`251		A		
`nomid`	BTREE	Non	Non		`nomid`	377		A

perso:

Code :

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

1
2
3
4
5
6
 
Nom		Type	Unique	Compressé Champ		Cardinalité	Interclassement	
PRIMARY		BTREE	Oui	Non	 `id`		171		A		
`userId`	BTREE	Non	Non	 `userId`	171		A		
`description`	FULLTEXTNon	Non	 `description`	171			
`background`	FULLTEXTNon	Non	 `background`	171

Aussi, à quoi me sert mes index en fulltext exactement ? Si je n'effectue aucune recherche sur ces champs texte, je peux avoir un champ texte sans l'indexer ?

[CinePhil]

Show ne possède que 3 valeurs possible, je dois l'indexé quand même ?

Non c'est inutile, l'index ne sera jamais utilisé. L'optimiseur jugera que c'est plus rapide de scanner les valeurs.

Tu peux nous donner un EXPLAIN de la requête ?

[FMaz]

Oula, à la première lecture j'ai failli ré-expliquer ma requête, puis j'ai cherché sur google et j'ai réalisé que s'était une commande. Comme quoi j'ai pas fini d'en apprendre

Donc voici l'EXPLAIN SELECT pour la sous-requête:

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 		he_fromto 	index 	PRIMARY,show 	msgid 	4 	 NULL 	108181 	Using where

Et voici l'EXPLAIN SELECT de la requête de "premier niveau" (?):

Code :

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

1
2
3
4
5
6
7
8
 
id 	select_type 	table 		type 	possible_keys 	key 	key_len ref 		rows 	Extra
1 	PRIMARY 	<derived2> 	ALL 	NULL 		NULL 	NULL 	NULL 		50 	Using temporary; Using filesort
1 	PRIMARY 	he 		eq_ref 	PRIMARY 	PRIMARY 4 	sq.msgid 	1 	Using where
1 	PRIMARY 	ft 		ref 	msgid 		msgid 	4 	he.id 		4 	Using where
1 	PRIMARY 	pc 		ref 	persoid,nomid 	persoid 4 	const 		4 	 
1 	PRIMARY 	p 		eq_ref 	PRIMARY 	PRIMARY 4 	ft.persoid 	1 	 
2 	DERIVED 	he_fromto 	index 	PRIMARY,show 	msgid 	4 	NULL 		108173 	Using where

Avec mes connaissances actuelles, je ne suis malheureusement pas certain d'être en mesure de bien interpréter ces résultats, donc toute commentaires, questions ou explication seront bienvenu -- et formateur.

Au cas ou ce serait requis, voici la requête exécutée, avec les valeurs.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT he.msg, he.date, he.id AS hid, he.type,
		         pc.nom, ft.fromto, ft.persoid,
		         p.sexe, p.imgurl, ft.`show`
                  FROM (SELECT msgid, `show`
                        FROM he_fromto
                        WHERE `show`!=0 AND persoid = 325
		        ORDER BY `msgid` DESC
		        LIMIT 0,50) as sq
	          INNER JOIN he AS he ON he.id=sq.msgid
	          INNER JOIN he_fromto AS ft ON ft.msgid = he.id
	          LEFT JOIN perso_connu AS pc ON pc.persoid = 325 AND pc.nomid = ft.persoid
	          LEFT JOIN perso AS p ON p.id = ft.persoid
	          ORDER BY he.`date` DESC, hid ASC, ft.fromto ASC , pc.nom ASC

Note: L'air de rien c'est long d'arranger les tabulations entre phpmyadmin et le forum... je sens que ca aurait été moins long de passer par le client mysql directement...

[CinePhil]

Visiblement, le problème se situe dans la table dérivée créée par la sous-requête :

id select_type TABLE type possible_keys KEY key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 50 USING TEMPORARY; USING filesort

Ce qu'il faudrait essayer, c'est carrément de faire une vraie table temporaire avec la sous-requête, éventuellement de l'indexer, puis de l'utiliser dans la jointure de la requête générale.

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
CREATE TEMPORARY TABLE sq
SELECT msgid, `show`
FROM he_fromto
WHERE `show` <> 0 AND persoid = 325
ORDER BY `msgid` DESC
LIMIT 0,50 ;
 
ALTER TABLE sq
ADD INDEX(msgid);
 
SELECT he.msg, he.date, he.id AS hid, he.type,
  pc.nom, ft.fromto, ft.persoid,
  p.sexe, p.imgurl, ft.`show`
FROM sq
INNER JOIN he AS he ON he.id = sq.msgid
  INNER JOIN he_fromto AS ft ON ft.msgid = he.id
    LEFT JOIN perso_connu AS pc ON pc.persoid = 325 AND pc.nomid = ft.persoid
    LEFT JOIN perso AS p ON p.id = ft.persoid
ORDER BY he.`date` DESC, hid ASC, ft.fromto ASC , pc.nom ASC

[FMaz]

Mon dieu, ca me semble tellement extrême que j'aurais jamais même osé tester une telle solution.

Est-ce que je dois exécuter les 3 requêtes dans le même "mysql_query()" ?

Car si je fais 3 requêtes séparés, le "FROM sq" ne sera pas lié, et si oui, il risque d'y avoir 2 ou 3 membres qui créer une nouvelle table temporaire en même temps. ( Je suis pas encore en transactionnel... ca viendra, mais pour l'instant j'ai des troubles de performance en MyISAM avec cette requête alors je veux régler ca avant de passer en innoDB + PDO )

[CinePhil]

Est-ce que je dois exécuter les 3 requêtes dans le même "mysql_query()" ?

Pour autant que je me souvienne, il faut lancer 3 mysql_query mais avec la même session MySQL :

Code PHP :

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
29
$sess_db = mysql_connect($server, $user$, $password);
$sql = "
  CREATE TEMPORARY TABLE sq
  SELECT msgid, `show`
  FROM he_fromto
  WHERE `show` <> 0 AND persoid = 325
  ORDER BY `msgid` DESC
  LIMIT 0,50
";
mysql_query($sql, $sess_db);
 
$sql = "
  ALTER TABLE sq
  ADD INDEX(msgid)
";
mysql_query($sql, $sess_db);
 
$sql = "
  SELECT he.msg, he.date, he.id AS hid, he.type,
    pc.nom, ft.fromto, ft.persoid,
    p.sexe, p.imgurl, ft.`show`
  FROM sq
  INNER JOIN he AS he ON he.id = sq.msgid
    INNER JOIN he_fromto AS ft ON ft.msgid = he.id
      LEFT JOIN perso_connu AS pc ON pc.persoid = 325 AND pc.nomid = ft.persoid
      LEFT JOIN perso AS p ON p.id = ft.persoid
  ORDER BY he.`date` DESC, hid ASC, ft.fromto ASC , pc.nom ASC
";
$resultset = mysql_query($sql, $sess_db);

[FMaz]

Je confirme que ca fonctionne sur ma copie de travail locale.

Je prépare une révision et je fait une tentative sur le site en "production" (c'est agréable de ne pas être un projet commercial, si je me plante, je perds pas mon emploi )

[Antoun]

Mon dieu, ca me semble tellement extrême que j'aurais jamais même osé tester une telle solution.

ça n'a rien d'extrême, quand tu penses que de toute façon MySQL implémente les tables dérivées en créant des tables temporaires.

Est-ce que je dois exécuter les 3 requêtes dans le même "mysql_query()" ?

Car si je fais 3 requêtes séparés, le "FROM sq" ne sera pas lié, et si oui, il risque d'y avoir 2 ou 3 membres qui créer une nouvelle table temporaire en même temps. ( Je suis pas encore en transactionnel... ca viendra, mais pour l'instant j'ai des troubles de performance en MyISAM avec cette requête alors je veux régler ca avant de passer en innoDB + PDO )

Les tables temporaires sont liées à une session, donc deux membres peuvent en créer en même temps sans qu'il n'y ait d'interférence.

[FMaz]

Merci pour la confirmation, c'est rassurant !

Bon donc voici les résultats préliminaires:

1.1 seconde pour effectuer les requêtes SQL. ( 0.15sec pour les fois consécutive lorsque la cache est en fonction )

C'est pas aussi mieux que j'aurais aimé, mais on va voir demain sous la charge de 60 utilisateurs simultané si c'est pire ou mieux. Si ca demeure stable, ca sera tout à fait satisfaisant.


En attendant, mes champs FULL INDEX, je peux effacer ca ? (Je fais aucun WHERE sur des champs textes...

[Antoun]

En attendant, mes champs FULL INDEX, je peux effacer ca ? (Je fais aucun WHERE sur des champs textes...

oui, les index FULLTEXT ne sont utilisés qu'avec MATCH.

[FMaz]

En tout cas, en attendant de voir les résultats, j'aimerais vraiment vous remercier pour votre investissement. Je crois que le contexte était assez pénible à appréhender, et j'ai trouvé que les commentaires qui ont été fait étaient très constructifs et pertinent.

Que ca fonctionne ou pas, j'aurais appris pas mal de truc ici, alors encore une fois, merci beaucoup !

[FMaz]

Je pense à ca,
en quoi est-ce une bonne chose de créer un index sur la table temporaire ?
Quel est le but de ca ?

la création de l'index dois prendre un certain temps
et la seule requête qui va utiliser cette table temporaire va utiliser toutes les lignes de toute facon...

... Je suis pas certain de comprendre ce détail.

[CinePhil]

Je pense à ca,
en quoi est-ce une bonne chose de créer un index sur la table temporaire ?
Quel est le but de ca ?

la création de l'index dois prendre un certain temps
et la seule requête qui va utiliser cette table temporaire va utiliser toutes les lignes de toute facon...

... Je suis pas certain de comprendre ce détail.

Essaie sans l'ajout d'index puis avec et compare le temps d'exécution.

Comme on fait une jointure entre la table temporaire et une autre table dans la requête principale, il est mieux de l'indexer. Mais si la table temporaire ne contient que très peu de valeurs différentes (peu de lignes dans la table), alors l'index sur la table temporaire sera probablement ignoré par l'optimiseur du SGBD.

Tu peux aussi voir ce que ça donne en faisant un EXPLAIN sur la dernière requête, avec et sans indexation de la table temporaire.

[FMaz]

C'est dur de faire une comparaison juste, car la première exécution est mise en cache, et forcément, les suivantes sont plus rapides.

Il existe un moyen simple de vider de cache MySQL avant chaque essaie ?

[Antoun]

C'est dur de faire une comparaison juste, car la première exécution est mise en cache, et forcément, les suivantes sont plus rapides.

Il existe un moyen simple de vider de cache MySQL avant chaque essaie ?

RESET QUERY CACHE (cf. http://dev.mysql.com/doc/refman/5.1/...intenance.html).

Tu peux aussi demander à ce que tes requêtes n'aillent pas dans le cache avec SQL_NO_CACHE (cf. http://dev.mysql.com/doc/refman/5.1/...in-select.html).