Discussion :

[Invité]

Bonjour à tous,

J'aimerai optimiser un requête qui me permet à la base de faire un filtrage par une réponse d'existence (ou d'appartenance) dans une autre table avec comme données mis en comparaison un champ reçu d'un left join et un champ du select sur un exists.

Dis comme ça, c'est pas clair... mais voici cette sql qui malheureusement prends trop de temps d’exécution quand il y a beaucoup d'entrées dans les tables :

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SELECT
    DISTINCT(nl.`id_news`), 
    n.*, 
    nl.*,
    LEFT(nl.`title`, 120) as title,
    (
        SELECT count(cn.`id_commentnews`)
        FROM `table_commentnews` cn
        WHERE cn.`news` = n.`id_news`
        AND cn.`actif` = 1
    ) as count_comments,
    n.`id_news` as `id`
FROM `table_news_lang` nl
LEFT JOIN `table_news` n
    ON (n.`id_news` = nl.`id_news`)
LEFT JOIN `table_correspondancecategorie` cc
    ON (n.`id_news` = cc.`news`)
 
WHERE 1=1
AND 
    EXISTS (
        SELECT 1
        FROM `table_categorie` pc
        JOIN `table_categorie_group` pcg
            ON (pc.`id_categorie` = pcg.`id_categorie` AND pcg.`id_group` = 1)
        WHERE cc.`categorie` = pc.`id_categorie`
    )
 
AND n.`id_shop` = 1
AND nl.`id_lang` = 1
AND n.`actif` = 1
AND nl.`actif_langue` = 1
AND TIMESTAMP(n.`date`) <= '2017-08-23 11:04:09'
ORDER BY n.`date` desc
LIMIT 240, 10;

Lors de mes tests, la sql est ultra rapide quand j'enlève la référence : WHERE cc.`categorie` = pc.`id_categorie`

En fait, je suppose que cc.`categorie` est lourde à mettre en valeur depuis le left join initial.

Merci pour votre aide

[CinePhil]

Sans toucher à la requête, il y a des choses en l'état qui peuvent expliquer sa lenteur :
1) DISTINCT est coûteux. Êtes-vous sûr d'en avoir besoin ?

2) SELECT *
Êtes-vous sûr d'avoir besoin de récupérer toutes les colonnes des tables n et nl ?
Vu que vous avez des conditions de restriction (clauses WHERE) sur les lignes de ces tables, je dirais que non.
=> Ne sélectionnez que les colonnes utiles à votre besoin.

3) SELECT count(cn.`id_commentnews`
Vous avez une requête dans le SELECT. Ça aussi, c'est très lent parce que la requête doit être exécutée pour chaque ligne de n.

4) WHERE 1=1
Inutile, même si ça ne coûte qu'un tout petit peu de temps.

5) AND TIMESTAMP(n.`date`) <= '2017-08-23 11:04:09'
L'utilisation de la fonction TIMESTAMP empêche l'utilisation par le SGBD d'un éventuel index sur la colonne date.

Au passage, c'est une mauvaise idée d'appeler une colonne "date" parce que c'est un mot clé du langage SQL.

6) ORDER BY coûte aussi mais si vous avez beaucoup de données en résultat de la requête, il vaut mieux quand même faire le tri dans la requête que par le logiciel applicatif.


Maintenant, analysons la requête...

7) Avez-vous besoin des LEFT JOIN ?
Si je comprends correctement votre requête, vous sélectionnez les news d'une certaine langue.
Il me semble qu'un INNER JOIN serait plus approprié.

Idem pour le second LEFT JOIN puisque vous cherchez les catégories des news qui correspondent à vos conditions de restriction.
Y aurait-il des news sans catégorie ?

Mais alors...
8) Pourquoi utiliser EXISTS sur la table des catégories ?
Une simple jointure ne suffirait-elle pas ?

Au final, je pense que cette requête devrait répondre à votre besoin :

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
21
22
SELECT
	nl.id_news,
	nl.[uniquement les colonnes nécessaires],
	n.[uniquement les colonnes nécessaires],
	LEFT(nl.title, 120) AS title,
	-- n.id_news AS id ==> Inutile car c'est le même que nl.id_news ve la jointure.
	COUNT(cn.id_commentnews) AS count_comments
FROM table_news n
INNER JOIN table_news_lang nl ON nl.id_news = n.id_news
INNER JOIN table_commentnews cn ON cn.news = n.id_news
LEFT OUTER JOIN table_correspondancecategorie cc ON cc.news = n.id_news
	INNER JOIN table_categorie pc ON pc.id_categorie = cc.categorie
		INNER JOIN table_categorie_group pcg 
			ON pcg.id_categorie = pc.id_categorie
			AND pcg.id_group = 1
WHERE n.id_shop = 1
	AND n.actif = 1
	AND n.`date` <= '2017-08-23 11:04:09'
	AND nl.actif_langue = 1
GROUP BY nl.id_news, [toutes les colonnes du SELECT sauf le COUNT]
ORDER BY n.`date` DESC
LIMIT 240, 10;

[Invité]

Merci vraiment pour ta réponse !

Je vais prendre le temps de retranscrire ta proposition à partir du point 7 car avant le problème de lenteur ne vient pas de ça et j'ai besoin de tout.
La lenteur est exclusivement sur le Where du select de l'EXISTS

Je vais essayer de comprendre ta proposition à ce sujet, car j'aime bien capter les choses avant.

Merci beaucoup ! et je te tiens au courant

[Artemus24]

Salut liandri.

Pour améliorer la performance de votre requête, utilisez des index dans vos tables.

Pour la table "table_categorie_group" :

Code :

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

index `idx_1` (id_categorie, id_group)

Pour la table "table_categorie" :

Code :

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

index `idx_2` (id_categorie)

Pour la table "table_news_lang" :

Code :

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

index `idx_3` (id_lang, actif_langue)

Pour la table "table_news" :

Code :

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

index `idx_3` (id_news, id_shop, id_actif, date)

En ce qui concerne la colonne "date", vu que vous n'utilisez pas les millionièmes de secondes, changez le type en mettant "datetime".

@+