Discussion :

[Benji76]

Bonjour à tous !!

Je vous écrit un peu désespéré car je n'ai jamais eu de telles (contre-)performances avec MySQL, et je ne sais pas du tout comment optimiser...

Voilà mon problème : j'ai un site de classement de jeux vidéo dont la base de données contient près de 2000 jeux (et ce n'est que le début), et pour chaque jeu entre 3 et 10 notes. Mon but est de faire une moyenne de ces notes et de retourner pour chaque jeu, entre autres renseignements, sa position dans la base de données.

Mes requêtes marchaient bien en prod avec une centaine de jeux, mais maintenant que l'on arrive à plusieurs milliers, chaque requête prend entre 2 et 3 secondes

Voici un exemple de requête que j'utilise :

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
SELECT games.name, engines.name AS console, engines.linker, games.score, 
games.id_game, games.img_name, comp_dev.company AS developpeur, 
comp_edi.company AS editeur, genres1.genre AS genre1, genres2.genre AS genre2, 
(SELECT COUNT(DISTINCT b.score) FROM games AS b WHERE b.score >= games.score) AS position, 
games.release_fr, games.release_us, games.release_jp, accounts.login, 
accounts.is_female, accounts.premium
FROM games
LEFT JOIN companies AS comp_dev ON (games.id_dev = comp_dev.id_company)
LEFT JOIN companies AS comp_edi ON (games.id_edi = comp_edi.id_company)
LEFT JOIN genres AS genres1 ON (genres1.id_genre = games.id_genre_1)
LEFT JOIN genres AS genres2 ON (genres2.id_genre = games.id_genre_2)
INNER JOIN engines ON (games.id_engine = engines.id_engine)
INNER JOIN accounts ON (games.id_add = accounts.id_acc)
WHERE games.visible = '1'
ORDER BY position DESC LIMIT 10

Je sais qu'elle est assez grosse, mais il faut noter que je ne l'apelle qu'une fois par page, et que je limite toujours les résultats à 10 / 20 ou 50.

Est ce que quelqu'un peut me dire ce qui ne va pas ? Comment pourrais je faire pour optimiser cela ? Y'a t'il seulement une solution, ou mon site est il destiné à ramer, notament en période de pointe ?

Je vous remercie pour votre aide !

[Antoun]

- quels sont les index existants ?
- tu devrais transformer ta sous-requête en jointure
- et écrire games.visible = 1

[Benji76]

Content de voir que tu n'as pas hurlé en tout cas

- Qu'entends tu par "tes index existants" ?
- Justement c'est ce que je voulais faire, mais je ne vois pas comment remplacer une sous requête par une jointure... Peut être peux tu m'éclairer ?
- Ok j'ai supprimé les guillemets

J'attends tes réponses avec impatience

[Antoun]

- Qu'entends tu par "tes index existants" ?

bah les index qui existent... je suppose que je dois interpréter ta réponse comme "je n'ai créé aucun index", ce qui expliquerait que tu pédales malgré le faible volume.
à vue de nez, je recommanderais le paquet suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE INDEX ON games (visible) ;
CREATE INDEX ON games (score) ;
CREATE INDEX ON games (id_dev) ;
CREATE INDEX ON games (id_edi) ;
CREATE INDEX ON genres (id_genre) ;
CREATE INDEX ON games (id_engine) ;
CREATE INDEX ON games (id_add) ;
CREATE INDEX ON companies (id_company) ;
CREATE INDEX ON games (id_genre_1) ;
CREATE INDEX ON games (id_genre_2) ;
CREATE INDEX ON engines (id_engine) ;
CREATE INDEX ON accounts (id_acc) ;

- Justement c'est ce que je voulais faire, mais je ne vois pas comment remplacer une sous requête par une jointure... Peut être peux tu m'éclairer ?

sur le principe, cf http://dev.mysql.com/doc/refman/5.0/...ubqueries.html
Dans ton cas, je ferais ça (sous réserve de test) :

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
SELECT games.name, engines.name AS console, engines.linker, games.score, 
games.id_game, games.img_name, comp_dev.company AS developpeur, 
comp_edi.company AS editeur, genres1.genre AS genre1, genres2.genre AS genre2, 
COUNT(DISTINCT b.score) AS position, 
games.release_fr, games.release_us, games.release_jp, accounts.login, 
accounts.is_female, accounts.premium
FROM games
LEFT JOIN games AS b ON b.score >= games.score
LEFT JOIN companies AS comp_dev ON (games.id_dev = comp_dev.id_company)
LEFT JOIN companies AS comp_edi ON (games.id_edi = comp_edi.id_company)
LEFT JOIN genres AS genres1 ON (genres1.id_genre = games.id_genre_1)
LEFT JOIN genres AS genres2 ON (genres2.id_genre = games.id_genre_2)
INNER JOIN engines ON (games.id_engine = engines.id_engine)
INNER JOIN accounts ON (games.id_add = accounts.id_acc)
WHERE games.visible = 1
GROUP BY games.name, -- ou games.id_jeu si tu as qqch comme ça
  engines.id_engine, accounts.id_acc
ORDER BY position DESC LIMIT 10

[Sivrît]

Code :

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

LEFT JOIN games AS b ON b.score >= games.score

peut même devenir

Code :

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

JOIN games AS b ON b.score >= games.score

puisqu'il doit au moins y avoir le jeu lui-même.

A défaut de supprimer la sous-requête, enlever le 'DISTINCT' qui ne me semble pas utile devrait aider.

Pour savoir où en sont les indexes (mais si tu poses la question je doute qu'il en ait beaucoup) :

Code :

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

1
2
3
 
DESC table; -- regarder la colonne 'Key'
SHOW INDEXES FROM table;

Dernière remarque, le LIMIT va surtout permettre de renvoyer moins de données, mais pas aider à limiter les traitements car il dépend d'un tri sur un résultat calculé et donc ne peut être appliqué qu'une fois que tout est fait.

[jeca]

Bonjour,

Sivrît =>

Dernière remarque, le LIMIT va surtout permettre de renvoyer moins de données, mais pas aider à limiter les traitements car il dépend d'un tri sur un résultat calculé et donc ne peut être appliqué qu'une fois que tout est fait.

Ce n'est pas vraiment ce qui est précisé dans le manuel mysql :
http://dev.mysql.com/doc/refman/5.0/...imization.html

[Sivrît]

Bonjour,

Sivrît =>


Ce n'est pas vraiment ce qui est précisé dans le manuel mysql :
http://dev.mysql.com/doc/refman/5.0/...imization.html

Comme souvent mieux vaut jeter à coup d'oeil à la version anglaise (malheureusement) plus complète :

If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.

Dans un cas comme celui-ci (pas d'index possible à moins de précalculer) c'est mieux que rien (le resultset est au moins plus petit) mais il ne faut pas compter dessus pour diminuer drastiquement les quantités de calculs et de données traitées d'une requête complexe.

[Benji76]

Je vous remercie pour tout vos conseils, je suis actuellement en déplacement professionel, mais je vais tester tout ça dès que je rentre et je vous posterai les résultats.

En ce qui concerne le temps d'execution, il s'avère qu'il y avait un problème du côté matériel. La requête est en fait plus rapide mais encore trop longue à mon goût.

J'ai tout de même deux questions concernant vos interventions

- Pour l"indexiation, est ce une action ponctuelle unique (je le fais une fois pour toutes) ou est ce que je répète cela, et quand (à chaque requête, périodiquement, etc...) ?

- Si j'ai la possibilité, est ce que traiter la sous requête comme requête à part serait performant ?
Je donne un exemple : comme je récupère les 5 meilleurs jeux, je fais une boucle et dans cette boucle je fais une requête pour déterminer le classement. Cela fait 5 requêtes , mais 5 requêtes rapides et légéres...
Quel est le meilleur choix ?

Merci pour votre aide

[Sivrît]

- Pour l"indexiation, est ce une action ponctuelle unique (je le fais une fois pour toutes) ou est ce que je répète cela, et quand (à chaque requête, périodiquement, etc...) ?

Une fois pour toute, ça fait parti du schéma de la base.

- Si j'ai la possibilité, est ce que traiter la sous requête comme requête à part serait performant ?
Je donne un exemple : comme je récupère les 5 meilleurs jeux, je fais une boucle et dans cette boucle je fais une requête pour déterminer le classement. Cela fait 5 requêtes , mais 5 requêtes rapides et légéres...
Quel est le meilleur choix ?

En fait dans ce cas autant trier dans le code, ça fait 0 requêtes Mais si les meilleurs sont récupérés via leur classement ça ne va pas aider... ce qui m'amène à remarquer que l'on se casse la tête pour rien : il suffit de trier directement sur le score ! J'airais dû y penser
Et plus de "LEFT JOIN game", de COUNT ni de sous requête. Il ne reste plus qu'a compter dans le code client ce qui ne doit pas être trop difficile.

On peut même pousser le vice à faire quelquechose comme :

Code :

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

1
2
3
SET @counter=0;
SELECT @counter:=@counter+1 AS classement, games.*
FROM games ORDER BY games.score;

En notant bien que 'classement' ne sera pas disponible dans le WHERE, c'est juste pour la sortie.

[Benji76]

Bonjour à tous !

Je suis rentré chez moi j'ai pu essayer vos différentes techniques.

Alors, à moins de m'y être mal pris avec les index (j'ai indexé tous les champs susceptibles de servir de clé à un moment ou un autre), je ne constate aucun changement... La requête était aussi longue avant qu'après, je ne comprend pas pourquoi.

Quand à remplacer la sous requête par la jointure, je dépasse la limite de temps fixée pour l'exécution de MySQL, donc c'est même pas la peine d'y penser

Quand à calculer la position uniquement en fonction du score, ça va si on demande le classement général, mais sinon... Il va bien falloir déterminer la position, donc je vais être obligé d'y venir. Par contre peut être que de classer sur une valeur est plus performant que de classer sur un calcul. C'est le dernier test qu'il me reste à faire et j'y vais de ce pas !