optimisation de requete

**maysa** · 21/04/2009, 10h53

Bonjour,

j'ai cette requete qui est très lente

Code :

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

1
2
3
4
5
 
SELECT * FROM auteur WHERE auteur.numAuteur IN (
	SELECT numAuteur FROM aEcrit
	JOIN editionlivre ON aEcrit.refLivre = editionlivre.refLivre
	WHERE editionlivre.numTypeCollection = 1)

je l'ai modifié ainsi

Code :

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

1
2
3
4
5
6
 
SELECT * 
	FROM auteur
	INNER JOIN aEcrit ON auteur.numAuteur = aEcrit.numAuteur
	INNER JOIN editionlivre EL ON aEcrit.refLivre = EL.refLivre
	WHERE EL.numTypeCollection =1

j'aurai voulu votre avis , c'est correct ?
Merci

**CinePhil** · 21/04/2009, 10h56

Ça me semble correct.
Bien sûr, pour les performances, il faut que toutes les tables soient correctement indexées.

**SQLpro** · 21/04/2009, 11h41

1) évitez le SELECT *, nommez les colonnes, ne mettez que celles utiles
2) y a t-il des contraintes FOREIGN KEY entre les tables ?
3) y a t-il un index sous chaque contraintes FOREIGN KEY ?
4) y a t-il un index sous numTypeCollection ?

Lisez l'article que j'ai écrit sur l'indexation : http://sqlpro.developpez.com/cours/quoi-indexer/

Et plus généralement sur le menu Optimisation de mon site

A +

**maysa** · 21/04/2009, 11h45

merci à tous les deux.

Hum oui il y a des foreign keys sur la table editionlivre, aEcrit et enfin oui toutes..
SQLPro, j'ai ton livre..mais chez moi

. Je vais aller voir sur le site

**Waldar** · 21/04/2009, 14h30

Il peut y avoir une différence en nombre de lignes pour les auteurs qui auront écrit plusieurs livres avec un numTypeCollection = 1.

Si ce qui vous intéresse sont les auteurs, votre première requête me paraît meilleure. Vous pourriez aussi l'écrire avec un EXISTS :

Code :

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

1
2
3
4
5
6
7
SELECT * FROM auteur
WHERE EXISTS (SELECT NULL
              FROM aEcrit
              INNER JOIN editionlivre
                ON aEcrit.refLivre = editionlivre.refLivre
              WHERE editionlivre.numTypeCollection = 1
              AND auteur.numAuteur = aEcrit.numAuteur)

**maysa** · 21/04/2009, 17h02

Merci

Ce que je veux ce sont les auteurs qui ont au moins écrit un livre... mais la première requete est beaucoup trop longue (au moins 40s)

**Yanika_bzh** · 21/04/2009, 17h37

Utilisez alors la derniere requete de Waldar, elle est parfaite pour cela

**pacmann** · 21/04/2009, 17h52

Salut !
Je sais pas trop pour le cas ici présent... mais il n'est pas sûr que la requête EXISTS soit plus rapide.
EXISTS impose à l'optimiseur l'ordre des tables pour l'exécution.
Si par exemple pour la troisième table le prédicat "numTypeCollection = 1" présente un fort facteur de filtre (sélectivité), la jointure simple sera beaucoup plus rapide.

(Après, je ne sais pas si c'est sensé dans ce cas

)

**Snipah** · 21/04/2009, 17h58

Maysa, as-tu consulté le plan d'execution de tes requetes? N'hésite pas à le partager, c'est plus pratique quand on parle d'optimisation

**Waldar** · 21/04/2009, 18h15

Avec les bons index toutes les requêtes doivent répondre rapidement.
Au niveau optimisation, les requêtes avec IN et EXISTS dans ce cas-là devraient produire un explain plan similaire.

**maysa** · 22/04/2009, 10h56

Envoyé par Snipah

Maysa, as-tu consulté le plan d'execution de tes requetes? N'hésite pas à le partager, c'est plus pratique quand on parle d'optimisation

Comment je fais pour l'avoir ? j'utilise sqlYog

**Snipah** · 22/04/2009, 11h46

Aucune idée, je n'ai jamais utilisé SqlYog. Il existe Toad for Mysql sinon.

Envoyé par Waldar

Avec les bons index toutes les requêtes doivent répondre rapidement.

C'est justement la question que je me pose, Maysa ne nous a pas parlé de ces index.

**maysa** · 22/04/2009, 12h04

Quasi tous les champs de ces tables sont indexés...

**maysa** · 22/04/2009, 12h14

Envoyé par Snipah

Aucune idée, je n'ai jamais utilisé SqlYog. Il existe Toad for Mysql sinon.

C'est justement la question que je me pose, Maysa ne nous a pas parlé de ces index.

comme je ne sais aps ce que tu veux exactement je ne peux pas te le donner : ) . C'est le temps d'éxécution de la requete ?

**Snipah** · 22/04/2009, 14h21

Non, tu l'as déjà donné, 40 secondes.
Ca me semble énorme pour une requête assez basique.
A défaut du plan d'exécution, tu pourrais nous fournir la volumétrie de tes tables (nombre de lignes par table) et la description des index définis sur ces tables?

Au passage, d'après ce que j'ai pu lire, pour obtenir le plan d'execution sous mysql il faut faire précéder ta requête du mot clé EXPLAIN. Il devrait t'afficher un tableau détaillant comment l’optimiseur va l'exécuter.

**maysa** · 23/04/2009, 09h36

alors j'ai fait un explain avant la requete (40 sec c'est approximatif, c'est meme plus long mais j'arrete car ça me plante le serveur)

donc voilà

Code :

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

1
2
3
4
5
---------------------------------------------------------------------------
id---select_type--table---type----possible_keys----keys--rows---extra
1|PRIMARY|auteur|ALL|(NULL)|NULL|67693-|using-where
2|DEPENDSUBQUERY|-editionlivre-|-ref-|refLivre,numTypeCollection|numTypeCollection|213166-|using-where
3|DEPENDSUBQUERY|-aEcrit-|-eq_ref-|PRIMARY,numAuteur,refLivre|PRIMARY|1|using-where;USING index

voila,pourtant dans editionlivre j'ai bien numTypeCollection d'indexé...

**Yanika_bzh** · 23/04/2009, 10h00

Pouvez vous donner la volumétrie de vos tables, leur DDL ainsi que la requete EXACTE que vous executez ?

Car faire planter un serveur avec une requete aussi simple est en effet tres intrigant.

**maysa** · 23/04/2009, 10h11

comment je vais pour avoir tout ça ?

la requete :

Code :

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

1
2
3
4
SELECT * FROM auteur WHERE auteur.numAuteur IN (
	SELECT numAuteur FROM aEcrit
	JOIN editionlivre ON aEcrit.refLivre = editionlivre.refLivre
	WHERE editionlivre.numTypeCollection = 1)

**Snipah** · 23/04/2009, 13h41

Le DDL de tes tables est leur script de création, qui contient donc leur description exacte : nom et type de chaque colonne.
Pour la volumetrie, un count(*) sur chacune des tables te donnera le nombre de ligne.

Il serait également intéressant de savoir ce qu'ont donné la requête proposée par Waldar et ta seconde requête :

Code proposé par Waldar :

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

1
2
3
4
5
6
7
SELECT * FROM auteur
WHERE EXISTS (SELECT NULL
              FROM aEcrit
              INNER JOIN editionlivre
                ON aEcrit.refLivre = editionlivre.refLivre
              WHERE editionlivre.numTypeCollection = 1
              AND auteur.numAuteur = aEcrit.numAuteur)

Code de ta seconde requête :

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

1
2
3
4
5
SELECT * 
	FROM auteur
	INNER JOIN aEcrit ON auteur.numAuteur = aEcrit.numAuteur
	INNER JOIN editionlivre EL ON aEcrit.refLivre = EL.refLivre
	WHERE EL.numTypeCollection =1