Discussion :

[jonjojo]

Bonjour,

J'ai une requête pourtant simple sur mon site, mais qui est vraiment très lente suite à la liaison avec une seconde table et j'aimerai savoir si je peux améliorer cela.

J'ai une table personnalites avec 80000 entrées et une table personnalites_pays avec 82000 entrées, car une personnalité peut avoir plusieurs pays.

personnalites
- id
- nom

id =>PRIMARY KEY
nom => INDEX

personnalites_pays
- id_personnalite
- id_pays

id_pays => INDEX
id_personnalite => INDEX
id_personnalite + id_pays => PRIMARY KEY

Ma requete est toute bête : je liste les personnalités en filtrant par un pays, avec une pagination (affichés 50 par 50), et ordonné par nom.

Code :

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

SELECT p.nom FROM personnalites AS p, personnalites_pays AS pp WHERE p.id = pp.id_personnalite AND pp.id_pays = 1 GROUP BY p.id ORDER BY p.nom ASC LIMIT 50000,50

Cette requête mets généralement plus de 2 secondes :/ (vérifié avec SQL_NO_CACHE)
J'ai vérifié avec EXPLAIN j'ai bien la key id_pays de personnalite_pays d'utilisée pour la jointure et la primary key id pour la table personnalites
Si j'enlève le ORDER BY p.nom, ça descend autour de 0.5s
Si j'enlève la jointure avec la table des pays, ça descend autour de 0.4s
Mes deux tables sont en MyISAM.
Le serveur dédié est pas mal pourtant (un ovh à 170€ par mois et la BDD tourne sur un SSD) c'est la seule requête lente que j'ai car la table personnalites est conséquente maintenant.

Est ce que c'est optimisable ?

Merci.

[aieeeuuuuu]

bonjour,

enlevez déjà le GROUP BY.

Combien y a-t-il de lignes dans personnalites_pays et combien avec id_pays = 1 ?

[jonjojo]

bonjour,

enlevez déjà le GROUP BY.

Combien y a-t-il de lignes dans personnalites_pays et combien avec id_pays = 1 ?

Merci de la réponse. J'ai 82000 entrées dans personnalite_pays. Et j'ai 54000 personnalités dont le pays = 1.

Effectivement sans le GROUP BY je descend à 0.6s (ça reste encore long mais c'est beaucoup mieux, si je mets un DISTINCT à la place c'est aussi long que le GROUP BY au passage), mais je l'avais mis ce GROUP BY pour une raison je pense (ma requête provient d'un moteur de recherche de personnalité et il est possible de filtrer par pas mal d'autres critères qui font des jointures avec d'autres tables et je crois que c'était utile pour ça).

[escartefigue]

Bonsoir

3 remarques au sujet de votre GROUP BY :

- selon la norme SQL, le groupage doit être cohérent avec les colonnes du SELECT, ce n'était pas le cas dans votre requête. MySQL est très permissif mais tout autre SGBD n'aurait pas accepté cette requête. A titre d'exemple, SELECT COL1, COL2, SUM(COL3) FROM TAB1 GROUP BY COL1, COL2 est correct, alors que SELECT COL1, SUM(COL3) FROM TAB1 GROUP BY COL2 ne l'est pas.

- faire un group by sans besoin d'agrégation (SUM, AVG, MIN, MAX...) ne présente aucun intérêt autre que pénaliser les performances

- comme votre critère de groupage était l'id, qui par définition est unique puisque PK, faire un GROUP BY ne sert à rien dans ce cas

[jonjojo]

Bonsoir

3 remarques au sujet de votre GROUP BY :
.........

Merci de la réponse. En fait en postant ici, j'ai volontairement simplifié ma requête; en vrai je faisais un SELECT p.id, p.nom etc.

En fait le GROUP BY est utile car j'ai aussi de lié aux personnalités des sociétés. Et une personnalité peut avoir plusieurs fois la même société de liée car une liaison personnalité - société comporte une date de début et une date de fin et un statut (en cours, terminé etc.), du coup si je n'ai pas le GROUP BY et que j'affiche les personnalité de telle société, ça va me sortir plusieurs fois la même personnalité.

[escartefigue]

Merci de la réponse. En fait en postant ici, j'ai volontairement simplifié ma requête; en vrai je faisais un SELECT p.id, p.nom etc.

En fait le GROUP BY est utile car j'ai aussi de lié aux personnalités des sociétés. Et une personnalité peut avoir plusieurs fois la même société de liée car une liaison personnalité - société comporte une date de début et une date de fin et un statut (en cours, terminé etc.), du coup si je n'ai pas le GROUP BY et que j'affiche les personnalité de telle société, ça va me sortir plusieurs fois la même personnalité.

En ce cas il faut utiliser DISTINCTet non GROUP BYpuisque vous n'utilisez aucune fonction d'agrégation (COUNT, AVG, MAX..), ni filtre sur une valeur agrégée (HAVING)

De plus, comme dans votre requête initiale, vous faites une equi-jointure avec la tables des pays, en filtrant sur l'identifiant PK du pays, il n'y a aucune chance d'avoir plusieurs lignes ...

[aieeeuuuuu]

en l’occurrence, je dirai même qu'il faut filtrer, pour ne garder que les profils "actif" et ainsi ne plus avoir de doublons.

[escartefigue]

en l’occurrence, je dirai même qu'il faut filtrer, pour ne garder que les profils "actif" et ainsi ne plus avoir de doublons.

il ne peut pas y en avoir car :

personnalites
- id
- nom

id =>PRIMARY KEYnom => INDEX

personnalites_pays
- id_personnalite
- id_pays
id_personnalite + id_pays => PRIMARY KEY

Code :

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

SELECT p.nom FROM personnalites AS p, personnalites_pays AS pp WHERE p.id = pp.id_personnalite AND pp.id_pays = 1 GROUP BY p.id ORDER BY p.nom ASC LIMIT 50000,50

Donc le groupage est non seulement incohérent avec le SELECT (spécificité MySQL) mais surtout inutile
Et la jointure n'est pas normalisée

[jonjojo]

Merci de la réponse, j'ai donc :

Remplacé le GROUP BY par DISTINCT partout ou j'ai pas de COUNT, SUM etc.
Et surtout utilisé le DISTINCT que lorsque j'en ai besoin.
Si on filtre uniquement par pays, je n'ai plus le DISTINCT donc, car une personnalité ne peut pas avoir plusieurs fois le même pays de lié.

Comme je disais ma requête est celle d'un moteur de recherche personnalité. Il est possible de filtrer par une dizaines de filtres, comme le pays, les genres, les sociétés liées, l'âge, le statut d'activité, etc.) du coup il peut y avoir des jointures avec 6 tables différentes au max. Certaines ont besoin d'un GROUP BY (afficher les personnalités qui ont le genre "Musicien" et "Acteur" par exemple)

SELECT p.nom FROM personnalite AS p, personnalite_genre AS pg WHERE p.id = pg.id_personnalite AND pg.id_genre IN (1,2) GROUP BY p.id HAVING COUNT(id_genre) = 2 ORDER BY p.nom ASC LIMIT 0,50

Du coup la requête peut être bien plus compliquée. Je génèré donc dynamiquement la requête en fonction des filtres de recherche demandés.

Au niveau des profils "actifs" j'ai pas bien saisie mais je suis obligé de mettre un DISTINCT si je fais une jointure avec ma table personnalites_societes par exemple car comme je dis plus haut, une personnalité peut être liée plusieurs fois à la même sociétée et elle ressort donc plusieurs fois sans le DISTINCT.

Après niveau performances, le DISTINCT c'est pas mieux que le GROUP BY.

SELECT DISTINCT(p.id), p.nom FROM personnalites AS p, personnalites_societes AS ps WHERE p.id = ps.id_personnalite AND ps.id_societe = 1 ORDER BY p.nom ASC LIMIT 0,50

[skuatamad]

Si les seuls données à afficher sont celles en provenance de la table personnalites alors il est probablement possible de modifier la génération de requête en faisant des tests d'existance plutôt que des jointures puis DISTINCT :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT p.nom 
  FROM personnalites AS p
 where 1 = 1
   and exists (select 1
                 from personnalites_pays AS pp 
                WHERE p.id = pp.id_personnalite 
                  AND pp.id_pays = 1)
 ORDER BY p.nom ASC 
 LIMIT 50000,50

En testant sur un ou 2 critères identifiés comme lents, ça doit pouvoir permettre de valider ou non le gain.

PS : Le 1 = 1 c'est pour initialiser la génération de la requête, après il suffit d'ajouter des AND EXISTS en fonction des critères plus facilement.

[jonjojo]

Si les seuls données à afficher sont celles en provenance de la table personnalites

Merci de la réponse. Malheureusement j'affiche aussi la nationalité.

Par curiosité j'ai fait des tests et ça aurait été mieux, je passe de 0.6s à 0.5s !

[Artemus24]

Salut jonjojo.

Optimiser un bête SELECT qui mets plus de 2 secondes

2 secondes, ce n'est pas la fin du monde. En général, le temps moyen d'une requête doit être inférieur à 1 seconde.
Je suppose que vous perdez plus de temps à vous décider si vous allez lancer la requête ou pas.

Mes deux tables sont en MyISAM.

Normalement cela devrait être plus rapide qu'avec le moteur InnoDB.

Et la jointure n'est pas normalisée

Tout à fait.

@ jonjojo : vous devriez utiliser les jointures, comme ci-après :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT    distinct t1.nom, ...

      FROM  personnalites      AS t1

INNER JOIN  personnalites_pays AS t2
        ON  t2.id_personnalite = t1.id
       AND  t2.id_pays         = 1

  ORDER BY  t1.nom ASC
     LIMIT  50000,50

En ce qui concerne le "group by", je suis d'accord qu'il ne sert à rien. Vous pouvez le remplacer par le "distinct" si cela est vraiment nécessaire.

Comme vous faites une jointure entre vos deux tables, je ne voie pas si vous avez utilisez les clef étrangères.
Autrement dit, "id_personnalite" doit être déclaré ainsi :

Code :

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

CONSTRAINT `FK_PERSONNALITES` FOREIGN KEY (`id_personnalite`) REFERENCES `personnalites` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

Pour vos index, je ne voie aucun problème, même sur la primary key de la table "personnalites_pays".
Je voie que vous faites un "order by" sur la colonne "nom" de la table "personnalites".
Pour éviter de trier, il y a une possibilité qui peut améliorer la performance de votre requête, mais peut dégrader les autres requêtes.
Il s'agit d'utiliser la "primary key", de la façon suivante :

Code :

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

1
2
primary key nom, id
unique index id

Après modification de vos deux index ci-dessus, il faudra réorganiser votre table en faisant :

Code :

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

alter table `personnalites` order by `nom`;

Trier une table qui est déjà dans l'ordre du tri vous fait gagner un peu de temps.
Le "order by" dans ce cas là n'est plus nécessaire. A vous de tester !

Est-ce que vous avez gagner quelques millisecondes ?

@+