Requête de sélection pour un site multilingue [Résolu]

[royalwear]

Bonjour,

je suis en train de créer un site multilingue et actuellement j'essaye d'améliorer les performances de mon application.

J'ai une requête qui consomme bcp de ressources, les résultats inutiles peuvent monter jusqu’à 50% !

Passons aux choses sérieuses ^^

Voici le schéma des tables utiles :


Voici la requête MYSQL utilisée pour récupéré les produits correspondant à une catégorie ainsi qu'à ses sous-catégories :

Code :

1
2
3
4
5
6
SELECT * FROM product a 
LEFT JOIN product_lang b ON a.id_product = b.product_id 
LEFT JOIN product_category_link c ON a.id_product = c.product_id 
WHERE  c.product_category_id IN 
(SELECT id_product_category FROM product_category WHERE `left` >= 1 AND `right` <= 58) 
AND  ( language_id LIKE 'en' OR language_id LIKE  _DEFAULT_LANG ) ORDER BY id_product ASC

RM : _DEFAULT_LANG = fr

Le problème avec cette requête (elle fonctionne mais trop gourmande), c'est la dernière ligne. En effet, je suis sensé récupérer les produits traduits dans la langues choisie par le visiteur. Sauf que si la traduction n'existe pas, il doit quand même récupéré le produit mais avec la langue par défaut (qui sera toujours présente).

Donc avec cette requête, dès que je vais choisir une langue (qui n'est pas celle par défaut), j'aurai à chaque fois des doublons inutiles ( une fois dans la langue choisie si elle existe et une fois dans la langue par défaut). Et quand on commence à avoir bcp de produits, la mémoire allouée est insuffisante pour récupéré tous les résultats de la requête ( +/- 15 000 enregistrements avec un fetchAll()).

Serait-il possible de modifier la requête pour éviter ses doublons et alléger mon fetchAll final ?

Bonne journée

[Drizzt [Drone38]]

Tu peux filtrer ta table product_lang.

Par exemple (c'est la première idée qui me vient, il y a certainement mieux), tu sélectionnes tous les product_id, language_id avec language_id = 'en' ou 'fr' et tu ajoutes un champ qui donne le nombre de lignes agrégées par product_id.

Il te reste plus qu'à récupérer uniquement les lignes avec : nombre = 1 or (nombre = 2 and language_id = 'en').

[aieeeuuuuu]

Bonjour,

Vous devriez commencer par remplacer le * par la liste exhaustives colonnes qui vous intéressent.
C'est plus lisible, donc plus maintenable, et surtout cela demande moins de ressources, en évitant de "manipuler" des informations inutiles, voire d’éviter d'aller chercher dans la table si toutes les informations dont vous avez besoin existent dans un index (index couvrant)

Ensuite, pour votre problème de langue par défaut, vous pouvez faire deux jointures externes, une pour la langue définie (tel que vous faites avec 'en' dans votre exemple) et une autre pour la langue par défaut

Il ne vous restera plus qu'a prendre les colonnes de la deuxième jointure (langue par defaut) si celles de la langue 'en' sont NULL, pour cela, vous pouvez utiliser COALESCE qui renvoie la première valeur non NULL.

Vous pouvez aussi remplacer votre sous requête par une jointure interne

Enfin, vous pouvez remplacer vos LIKE par des =

ça donnerait quelque chose comme :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT [les colonnes utiles],
COALESCE(b.name, langueDefaut.name) AS name,
COALESCE(b.description, langueDefaut.Description) AS description,
...
FROM product a 
LEFT JOIN product_lang b 
    ON a.id_product = b.product_id 
LEFT JOIN product_lang  langueDefaut
    ON a.id_product =  langueDefaut.language_id
LEFT JOIN product_category_link c ON a.id_product = c.product_id 
INNER JOIN product_category pc
    ON pc.id_product_category =  c.product_category_id
WHERE 
    `left` >= 1 
    AND `right` <= 58
    AND  b.language_id = 'en' 
    AND langueDefaut.language_id LIKE  _DEFAULT_LANG
ORDER BY id_product ASC

[royalwear]

Bonjour,

merci à tous les deux pour vos réponses.

@Drizzt [Drone38] : Pas bête, je vais tester.

@aieeeuuuuu : Merci pour les différentes optimisations proposées, c'est toujours bon à savoir

Je vais regarder de plus près, l'explication et l'exemple que tu proposes, surtout la fonction COALESCE que je ne connais pas.

Concernant le *, c'est parce que dans ce cas-ci j'utilise toutes les colonnes récupérées (construction de mon objet php). Sinon, oui j'ai la possibilité de ne sélectionner que certaines colonnes ^^

Enfin, vous pouvez remplacer vos LIKE par des =

En effet

Merci pour vos conseils

[royalwear]

C'est de nouveau moi ^^

Je viens de tester ta solution, la requête fonctionne mais je n'obtient que les produits qui ont une traduction en Anglais. Ceux qui n'ont pas de traductions Anglaise mais dans la langue par défaut ne sont pas sélectionner.

Une petite idée d’où pourrait venir l'erreur ?

Voici la requête obtenue après les changements :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT a.reference, a.reference_partner, a.price, a.measure, a.discount, a.available, a.type, a.STATUS, a.new, a.parent_id, a.partner_id,
COALESCE(b.name, defaultLang.name) AS name,
COALESCE(b.description_short, defaultLang.description_short) AS description_short,
COALESCE(b.description, defaultLang.description) AS description,
COALESCE(b.keywords, defaultLang.keywords) AS keywords 
FROM product a
LEFT JOIN product_lang b ON a.id_product = b.product_id 
LEFT JOIN product_lang defaultLang ON a.id_product = defaultLang.product_id 
LEFT JOIN product_category_link c ON a.id_product = c.product_id 
INNER JOIN product_category pc ON pc.id_product_category = c.product_category_id 
WHERE `left` >= 1 AND `right` <= 58 
AND b.language_id = 'en' 
AND defaultLang.language_id = 'fr' 
ORDER BY id_product ASC

[ced]

Oui, c'est parce qu'il faut mettre la condition sur la langue anglaise dans la jointure et non pas dans le WHERE :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.reference, a.reference_partner, a.price, a.measure, a.discount, a.available, a.type, a.STATUS, a.new, a.parent_id, a.partner_id,
COALESCE(b.name, defaultLang.name) AS name,
COALESCE(b.description_short, defaultLang.description_short) AS description_short,
COALESCE(b.description, defaultLang.description) AS description,
COALESCE(b.keywords, defaultLang.keywords) AS keywords 
FROM product a
LEFT JOIN product_lang b ON a.id_product = b.product_id AND b.language_id = 'en' 
LEFT JOIN product_lang defaultLang ON a.id_product = defaultLang.product_id 
LEFT JOIN product_category_link c ON a.id_product = c.product_id 
INNER JOIN product_category pc ON pc.id_product_category = c.product_category_id 
WHERE `left` >= 1 AND `right` <= 58 
AND defaultLang.language_id = 'fr' 
ORDER BY id_product ASC

A lire à ce sujet : http://blog.developpez.com/cinephil/...on-de-restric/

[vmolines]

Vous filtrez une colonne issue d'une table provenant d'une jointure externe.

[royalwear]

En effet, je viens de comprendre le tout grâce à ca

Merci bcp, ca fonctionne ^^ Ma bd va pouvoir souffler

[aieeeuuuuu]

mea culpa, ça m'a échappé en modifiant la requête, mais effectivement, avec la condition sur la langue dans la clause WHERE, ça doit marcher beaucoup moins bien