pour les pros mysql

[jeroen]

Salut !
Pour un passage sur un hébergeur payant, je chercha a optimiser un peu mes acces à la base. (Chez free j'avais aucun pb)

Mon site traite de topos et de photos associés a ces topos

Ma base : 2 tables (simplifiée)

Code: Tout sélectionner

topos
-----
id
nom

photos
-------
id
id_topo
photo

Je souhaite afficher sur ma page d'accueil les 10 derniers topos avec le nbre de photos associées.

Pour l'instant je sélectionnais les 10 derniers topos et je faisais une boucle pour selectionner le nbre de photos associées.

Je peux faire tout d'un coup avec la requete suivante :

Code: Tout sélectionner

SELECT topos.id,topos.nom,count(photos.id) as nbp FROM topos LEFT JOIN photos ON photos.id_topo=topos.id GROUP BY topos.id ORDER BY topos.id DESC limit 10

Le problème est que c'est extremement long. J'ai l'impression qu'il balaye toute la table topos (500 entrees).

Y'a un truc que je peux optimiser ?
Comment feriez vous ?

[XenonDelee]

ben moi je suis loin d'être un pro

( je suis même impressionné par tes requêtes ! j'savais même pas que ça pouvait exister des requêtes aussi chiadés ... )

mais à mon petit niveau, j'aurais fait :

je sélectionne l'id du premier topo un puis je cherche dans photos celle qui correspondent à cet id

et ça en boucle pour tes dix topos ...

Comme ça ça balaye pas toute la table topos ...

Mais c'est peut-être idiot ?

[XenonDelee]

ps : pas mal ton site !

en plus je trouve que tu as parfaitement intégré tes addsens ! nickel chrome ...

Bravo

[Dr DLP]

Tu as essayé avec un WHERE plutôt qu'un LEFT JOIN ?
Moi j'essaierais ça :

Code: Tout sélectionner

SELECT t.id , t.nom , count(p.id) as nbp
FROM topos as t , photos as p
WHERE p.id_topo = t.id
GROUP BY t.id
ORDER BY t.id DESC limit 10

Enfin ... Si je ne me suis pas embrouillé avec les lettres

[jeroen]

> je sélectionne l'id du premier topo un puis je cherche dans photos celle qui correspondent à cet id

Ce que je faisais avant...

> ps : pas mal ton site

Merci pour les encouragements )

> Tu as essayé avec un WHERE plutôt qu'un LEFT JOIN ?

J'ai bien essayé mais non : les topos sans aucune photo associée ne sont pas pris dans la selection (WHERE p.id_topo = t.id)

C'est pour cette raison que je me suis orienté vers le LEFT JOIN

Je me pose une question.
Est ce que seul le temps de connexion a la base est à optimiser ? ou est-ce que le nombre de requêtes a la base a son importance aussi ?

une selection des topos puis une boucle pour la selection des photos ? Ca pose un pb, non (11 requêtes select)

[sgaze]

Tu as créé des index respectivement sur les colonnes topos.id, photos.id et photos.id_topo ?

[sgaze]

J'ai réécris ta requête avec une syntaxe qui m'est plus familière mais ça change pas le fond :

Code: Tout sélectionner

SELECT t.nom, count( p.photo )
FROM photos p, topos t
WHERE t.id = p.id_topos
GROUP  BY t.nom
order by t.id desc limit 10

Pour tester, j'ai chargé 876 lignes dans topos et 9579 lignes dans photos

Sans index, cette requête met 2.9 secondes
avec un index (primary key) sur les colonnes id : 0.29 secondes
avec un index sur les colonnes id mais aussi sur id_topos : 0.24 secondes

En bref, si tu as déjà des index sur ta table, c'est chelou...

[jeroen]

samgaz, ta requête ne marche pas pour moi car elle ne renvoie pas les topos qui n'ont aucune photo associée !

[sgaze]

samgaz, ta requête ne marche pas pour moi car elle ne renvoie pas les topos qui n'ont aucune photo associée !

Désolé, j'avais pas reconnu la jointure externe car je connais plus oracle .

Sinon ta requête passe en 0,0012 secondes chez moi.

Tu confirmes que tu as des index ?

[jeroen]

J'avais des index mais pas au bon endroit!
J'ai essayé de modifier et j'ai divisé le temps de la requête par 100 !!!!
Toi qui a l'aire de t'y connaitre, est ce que tu peux m'expliquer ou est ce qu'il faut mettre des index dans ce cas précis, et en général ?

Merci !

[sgaze]

Les index sont l'équivalent d'une table des matières. Si tu recherches une ligne précise dans une table, ce sera plus rapide de connaître son emplacement en parcourant un index de quelques ko qu'en scannant toute la table (quelques centaines de ko par exemple) jusqu'à tomber sur la bonne ligne.

Il faut donc placer les index sur les colonnes qui te servent à te positionner dans une table. Typiquement, les ID, les colonnes sur lesquelles ont fait une jointure (comme id_topo).

Inutile de mettre un index sur toutes les colonnes car les index prennent de la place aussi. Ici un index sur nom_topo ne sert à rien. Sauf si tu te mets à faire des requêtes avec des clauses du genre WHERE NOM_TOPO="toto"...

Un index est très efficace sur les colonnes ayant des valeurs très diversifiées. Un ID c'est l'idéal car chaque valeur est unique.

A l'inverse un index sur une colonne contenant 50% de 1 et 50% de 2 ne sert pas à grand chose. Ce serait comme créer une table des matières pour un livre de 300 pages dont la moitié des pages porte le numéro 1 et l'autre le numéro 2...

[jeroen]

C'est trés clair.
Merci pour tout !