Discussion :

[Kalmani]

Bonjour,

j'essaie d'optimiser la vitesse d'exécution du site de ma société, quand tout à coup, j'aperçois ça :

Code sql :

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
select table_produits.prd_id, table_produits.reference, table_produits.prix_promo, table_produits.prix_ttc, 
table_prd_categories.cat_code, table_produits.nouveauter, table_produits.destockage, table_produits.ref_couleur,
 table_produits.sortie_de_collection, table_produits.top_vente, table_produits.coup_coeur, table_produits.vente_flash 
 
from table_produits, table_descriptions, table_prd_categories 
 
where 1 
and table_produits.prd_id = table_descriptions.prd_id 
and table_produits.prd_id = table_prd_categories.prd_id 
and table_produits.actif=1 
and (table_prd_categories.cat_code like '__20%' ) 
and (table_prd_categories.cat_code like '10%' OR table_prd_categories.cat_code like '30%' OR table_prd_categories.cat_code like '20%' ) 
 
 
and ((table_produits.prd_id,'238') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'149') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'150') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'321') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
and ((table_produits.prd_id,'54') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'55') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'56') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
and ((table_produits.prd_id,'196') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'82') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
and ((table_produits.prd_id,'318') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
and ((table_produits.prd_id,'90') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
and ((table_produits.prd_id,'308') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
and ((table_produits.prd_id,'309') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
and ((table_produits.prd_id,'310') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
and ((table_produits.prd_id,'311') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
and ((table_produits.prd_id,'17') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)) 
 
 
AND sortie_de_collection != 1 
and table_prd_categories.cat_code not like '00%' 
and table_produits.prix_promo BETWEEN 0 and 1500 
 
GROUP BY table_produits.reference order by table_produits.prix_promo ASC,table_produits.prix_ttc ASC ,table_produits.reference 
 
LIMIT 0,12

Les champs sélectionnés OK, les tables OK, les quelques première conditions OK, group by, limit, pas de problème... Mais les pavés de conditions (celles que j'ai séparé des autres)! C'est une catastrophe!

Je sais que je peux faire des conditions du type WHERE champ IN (valeur, valeur_2 .... ) mais je n'ai jamais touché à des requêtes sous cette forme... Vous savez, avec des parenthèses partout et une espèce de jointure bizarre...

Je ne demande pas qu'on m'écrive une requête toute faite, juste qu'on m'explique, histoire d'essayer de comprendre comment faire

Merci par avance!

[Kalmani]

Re-bonjour!

Et bien mon post n'a pas l'air franchement populaire :p

Je n'ai pas chômé pour autant sur le sujet et j'ai réussi à apporter quelques améliorations à ma requête!

Je passe donc de ça:

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
 
SELECT table_produits.prd_id, table_produits.reference, table_produits.prix_promo, table_produits.prix_ttc, 
table_prd_categories.cat_code, table_produits.nouveauter, table_produits.destockage, table_produits.ref_couleur,
 table_produits.sortie_de_collection, table_produits.top_vente, table_produits.coup_coeur, table_produits.vente_flash 
 
FROM table_produits, table_descriptions, table_prd_categories 
 
WHERE 1 
AND table_produits.prd_id = table_descriptions.prd_id 
AND table_produits.prd_id = table_prd_categories.prd_id 
AND table_produits.actif=1 
AND (table_prd_categories.cat_code LIKE '__20%' ) 
AND (table_prd_categories.cat_code LIKE '10%' OR table_prd_categories.cat_code LIKE '30%' OR table_prd_categories.cat_code LIKE '20%' ) 
 
 
AND ((table_produits.prd_id,'238') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'149') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'150') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'321') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'54') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'55') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'56') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'196') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'82') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'318') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'90') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'308') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'309') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'310') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'311') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'17') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)) 
 
 
AND sortie_de_collection != 1 
AND table_prd_categories.cat_code NOT LIKE '00%' 
AND table_produits.prix_promo BETWEEN 0 AND 1500 
 
GROUP BY table_produits.reference ORDER BY table_produits.prix_promo ASC,table_produits.prix_ttc ASC ,table_produits.reference 
 
LIMIT 0,12

à ça :

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
 
SELECT table_produits.prd_id, table_produits.reference, table_produits.prix_promo, table_produits.prix_ttc, 
table_prd_categories.cat_code, table_produits.nouveauter, table_produits.destockage, table_produits.ref_couleur,
table_produits.sortie_de_collection, table_produits.top_vente, table_produits.coup_coeur, table_produits.vente_flash 
 
from table_produits
right join table_prd_description on table_produits.prd_id = table_prd_description.prd_id 
right join table_prd_categories on table_produits.prd_id = table_prd_categories.prd_id
right join table_prd_infos on table_produits.prd_id = table_prd_infos.prd_id
 
WHERE 1 
AND actif=1 
and cat_code REGEXP '^[0-9]{2}20'
and cat_code REGEXP '^10|^30|^20%'
 
 
AND ((table_produits.prd_id,'238') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'149') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'150') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'321') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)) 
AND ((table_produits.prd_id,'54') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'55') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'56') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)) 
AND ((table_produits.prd_id,'196') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'82') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)) 
AND (table_produits.prd_id,'318') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)
AND (table_produits.prd_id,'90') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) 
 
and champs_options_id NOT IN (308,309,310,311,17)
 
AND sortie_de_collection != 1 
AND cat_code NOT LIKE '00%' 
AND prix_promo BETWEEN 0 AND 1500 
 
GROUP BY reference ORDER BY prix_promo ASC,prix_ttc ASC ,reference 
 
LIMIT 0,12

J'ai l'impression que c'est nettement mieux mais je me casse les dents sur cet espèce de pavé :

Code :

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

1
2
3
4
5
AND ((table_produits.prd_id,'238') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'149') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'150') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'321') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)) 
AND ((table_produits.prd_id,'54') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'55') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'56') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)) 
AND ((table_produits.prd_id,'196') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'82') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)) 
AND (table_produits.prd_id,'318') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)
AND (table_produits.prd_id,'90') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos)

J'ai déjà tenté de faire des clauses IN telles que

Code :

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

AND champs_options_id IN(238,326,326,149,150,321)

pour la première clause par exemple.

Cela fonctionne si je n'en met qu'une, mais étrangement si je met plusieurs conditions de ce type à la suite le serveur ne me retourne aucun résultat...

Je vous supplie à genoux, mon cerveau se fait harceler de AND et de OR à longueur de journée !

Merci de votre patience.

[punkoff]

bonjour,

Si vous aviez un peu présenté la modélisation ca aurai été plus simple.

Sinon :
il ne sert à rien de faire une jointure externe, si on met les conditions de jointures dans la clause where, car ceci transforme la jointure externe en jointure interne.


Votre transformation de :

Code :

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

1
2
3
4
5
6
 
AND ((table_produits.prd_id,'308') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'309') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'310') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'311') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'17') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos))

en :

Code :

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

1
2
3
 
RIGHT JOIN table_prd_infos ON table_produits.prd_id = table_prd_infos.prd_id
where champs_options_id NOT IN (308,309,310,311,17)

Est fausse au vu des cardinalité entre les deux tables.

Il faut passer par une clause not exists (vu que c'est une négation 1 seule suffira)



Pour cette partie :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
AND ((table_produits.prd_id,'238') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'326') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'149') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'150') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'321') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'54') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'55') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'56') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'196') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'82') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'318') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'90') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'308') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'309') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'310') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'311') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) ) 
AND ((table_produits.prd_id,'17') NOT IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos))

Il faut passer par une clause EXISTS entre chaque opérateur AND.


On ne pourra pas bien faire mieux, tant que vous n'expliquerez pas la modélisation.

[Kalmani]

Bonjour et merci pour votre réponse.

Désolé pour les infos manquantes, je débute ici :p

Voici les quelques tables concernées :

table_produits:

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
CREATE TABLE IF NOT EXISTS `table_produits` (
  `prd_id` mediumint(8) unsigned NOT NULL auto_increment,
  `date_entree` date NOT NULL default '0000-00-00',
  `reference` varchar(30) default NULL,
  `ref_couleur` varchar(50) default NULL,
  `prix_ttc` float(10,2) unsigned NOT NULL default '0.00',
  `prix_promo` float(10,2) unsigned NOT NULL default '0.00',
  `actif` tinyint(3) unsigned NOT NULL default '1',
  `nouveauter` tinyint(3) unsigned NOT NULL default '0',
  `destockage` varchar(255) NOT NULL,
  `coup_coeur` varchar(10) NOT NULL,
  `top_vente` varchar(10) NOT NULL,
  `vente_flash` varchar(10) NOT NULL,
  `sortie_de_collection` varchar(11) NOT NULL,
  PRIMARY KEY  (`prd_id`));

Cette table, comme son nom l'indique, contient toutes les infos basiques des produits.


table_description:

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS `table_description` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `prd_id` mediumint(8) unsigned NOT NULL default '0',
  `langue` char(2) NOT NULL,
  `libel` varchar(200) NOT NULL,
  `description` mediumtext NOT NULL,
  `keywords` mediumtext NOT NULL,
  PRIMARY KEY  (`id`)

Cette table renvoie une description selon la langue, avec un nuage de mot clés, lui aussi selon la langue de l'utilisateur, pour le formulaire de recherche.


table_prd_categories:

Code :

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

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `table_prd_categories` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `prd_id` mediumint(8) unsigned NOT NULL default '0',
  `cat_code` varchar(125) NOT NULL,
  PRIMARY KEY  (`id`)
)

Cette table relie les ids des produits aux codes de leurs catégories. Le créateur du site a préféré ne pas fonctionner que par ids de catégories, mais par code. Un code est composé de 6 chiffres.
Pour un code 012345 on a:
- 01 => type de produit.
- 23 => homme/femme/mixte/garçon/fille/enfant mixte.
- 45 => marque du produit.
(d'où les expressions régulières dans la dernière requête que j'ai présenté)


table_prd_infos:

Code :

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

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `bt_prd_infos` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `prd_id` mediumint(8) unsigned NOT NULL default '0',
  `prd_champs_id` int(11) NOT NULL,
  `champs_options_id` int(11) NOT NULL
  PRIMARY KEY  (`id`)

Cette dernière table est celle qui contient toutes les "options" des produits. prd_champs_id contient par exemple l'id de l'option "couleur" et champs_options_id contient l'id de la valeur "bleue".


Pour en revenir à mes jointures, oui elles sont peut être inutiles, mais je n'en ai jamais fait jusqu'à présent Tout conseil est le bienvenu !

Alors, avez-vous une idée? Je rappel que ce que j'ai fonctionne, mais le temps de traitement est parfois assez long. Je suppose que c'est due en partie aux requêtes imbriquées, c'est pourquoi la solutions de EXISTS et NOT EXISTS me semble plus ou moins équivalente à celle que j'ai déjà.

[punkoff]

la solution avec les exists diminueront le nombre de sous-requête exécutées.


La table catégorie est très mal modélisée.

La table table_prd_infos souffre aussi d'un problème de conception qui ne fera qu'empirer quand vous aurez plus de volumétrie

[Kalmani]

Je ne doute pas du fait que ça soit très mal modélisé (captain obvious m'a déjà passé un coup de fil à ce sujet ).
Niveau volume, on a déjà pas mal de données et ça se ressent dans la lenteur du site.

Mais y a-t'il une solution à ça ?

[punkoff]

Mais y a-t'il une solution à ça ?

Une solution à quoi ?

[Kalmani]

Et bien c'est sans doute mal modélisé.
Mais personnellement je ne vois pas comment stocker les options produits différemment (radicalement).

Edit: Déjà pour les catégories, les stocker dans la table_produits serait avantageux je suppose ?

[punkoff]

Cette table relie les ids des produits aux codes de leurs catégories. Le créateur du site a préféré ne pas fonctionner que par ids de catégories, mais par code. Un code est composé de 6 chiffres.
Pour un code 012345 on a:
- 01 => type de produit.
- 23 => homme/femme/mixte/garçon/fille/enfant mixte.
- 45 => marque du produit.
(d'où les expressions régulières dans la dernière requête que j'ai présenté)

On casse ici la 1ere forme normale.

Il en découle que les recherche sur les "catégories" deviennent une usine à gaz (lire table scan systématique).

Si on décompose correctement le code on va se retrouver avec 3 nouvelles tables de références :
- type de produit
- Marque
- catégorie

Mais vos ralentissements se trouveront surement sur la partie de IN imbriqué au vu de la table produit_info

[Kalmani]

Mais vos ralentissements se trouveront surement sur la partie de IN imbriqué au vu de la table produit_info

C'est le cas. A partir du moment où l'on implique un paramètre qui met en relation la table prd_infos, c'est d'une lenteur abominable.

La solution ne serait donc pas d'améliorer la requête mais bien de modifier la façon dont on stock les informations?

J'aimerais que ça soit si simple, sauf qu'en deux ans dans ma boite, la solution "restructuration de la base de données" est "trop longue" et donc tabou.

Quels sont donc mes choix ? Je tente en vain d'améliorer cette fichue requête jusqu'au jour où le site passera off et je pourrais dire "je vous avais prévenue", mais en attendant, on me demande juste d'accélérer les processus de recherche.

Au vue et considérant la structure actuelle (que l'on ne pourra pas modifier), comment pourrais-je donc améliorer ma requête ?
Y a-t'il un moyen d'éviter les requêtes imbriquées?

[punkoff]

Y a-t'il un moyen d'éviter les requêtes imbriquées?

Non, juste de les limités, cf mon msg plus haut.

[Kalmani]

Avec EXISTS ?

Je n'ai jamais utilisé cette structure jusqu'à maintenant.

J'ai fait un essaie et ai modifier la condition

Code :

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

and ((table_produits.prd_id,'152') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos) OR (table_produits.prd_id,'19') IN (SELECT table_prd_infos.prd_id, table_prd_infos.champs_options_id FROM table_prd_infos))

en ceci

Code :

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

and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (152,19))

Malheureusement je n'obtient pas le même résultat! Je m'y prend mal ?

Edit: Quand je modifie toutes les conditions, là ça devient carrément la cata! Plus rien ne correspond.

[punkoff]

il manque la condition de jointure entre la sous requete et la requete principale (id_produit)

[Kalmani]

Effectivement!

Je me retrouve au final avec ça:

Code :

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

1
2
3
4
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (152,19) AND prd_id = table_produits.prd_id)
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (48) AND prd_id = table_produits.prd_id)
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (180) AND prd_id = table_produits.prd_id)
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (84,83) AND prd_id = table_produits.prd_id)

J'ai pris le parti de laisser un IN même s'il n'y a qu'une valeur (pour faciliter côté php), peut être est-ce plus gourmand en ressources ?

En tout cas merci pour cette belle avancée

[Kalmani]

Désolé pour le double post, je met à dispo ma nouvelle requête toute belle:

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
select table_produits.prd_id, table_produits.reference, table_produits.prix_promo, table_produits.prix_ttc, 
table_prd_categories.cat_code, table_produits.nouveauter, table_produits.destockage, table_produits.ref_couleur, 
table_produits.sortie_de_collection, table_produits.top_vente, table_produits.coup_coeur, table_produits.vente_flash 
from table_produits
right join table_prd_description on table_produits.prd_id = table_prd_description.prd_id 
right join table_prd_categories on table_produits.prd_id = table_prd_categories.prd_id
where 1 
and actif = 1 
and cat_code REGEXP '^[0-9]{2}10|^[0-9]{2}20'
and cat_code  REGEXP '^[0-9]{4}14'
and cat_code REGEXP '^10|^30|^20%' 
 
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (152,19) AND prd_id = table_produits.prd_id)
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (48) AND prd_id = table_produits.prd_id)
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (180) AND prd_id = table_produits.prd_id)
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (84,83) AND prd_id = table_produits.prd_id)
and NOT EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (308,309,310,311,17) AND prd_id = table_produits.prd_id)
 
AND sortie_de_collection != 1 
and cat_code not like '00%' 
and prix_promo BETWEEN 0 and 1500 
GROUP BY reference
order by prix_promo ASC,prix_ttc ASC,reference 
LIMIT 0,12

Ça me semble plutôt pas mal

- Ai-je eu raison de supprimer les alias (je crois que ça s'appel comme ça) devant les champs concernés dans les clauses WHERE ? Est-ce que ça a un impact sur le temps d'exécution ?

- Les expressions régulières sont-elles plus rapides au vue de la structure de la table des catégories que j'ai décrit plus haut ?

- Les deux jointures sont-elles judicieuses ?

Après ces trois questions, je crois que j'en aurais définitivement terminé avec cette requête

Merci encore !

[Dendrite]

sauf qu'en deux ans dans ma boite, la solution "restructuration de la base de données" est "trop longue" et donc tabou.

Qu'y a-t-il de long dans cette histoire ?
Ce qui est fait salement est long, très très long, et ne peut que devenir de plus en plus long. Au travail, on a souvent ce genre de pression, assez paradoxale, car pour une rentabilité à court terme, on perd une fortune à long terme.

Si la base a été modélisée par de mauvais amateurs, elle sera toujours un boulet.

Lis ce qui suit, saute aux exemples si vraiment c'est du chinois, et commence à modéliser une nouvelle base propre selon le précepte : une table = une entité. Ce ne sera pas très intuitif, mais tu feras des vues ensuite pour l'intuitivité.

http://fr.wikipedia.org/wiki/Forme_n...ationnelles%29

Tu verras après si tu peux reprendre les données rapidement dans ta nouvelle base.

[Kalmani]

Bonjour !

Je dépoussière ce post

Merci pour ta réponse. Par contre, pour être tout à fait franc, je n'ai pas compris grand chose

Ci-dessous, une petite modélisation des tables dont j'ai parlé précédemment :


Vraiment désolé aux puristes si cette modélisation semble "vide", je ne sais vraiment pas comment on fait ce genre de choses... d'habitude je me contente de faire à peu près ça sur papier, sans plus...

Mon problème est toujours le même. Voici une requête qui met environ 10 secondes à s'exécuter :

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
select table_produits.prd_id, table_produits.reference, table_produits.prix_promo, table_produits.prix_ttc, 
table_prd_categories.cat_code, table_produits.nouveauter, table_produits.destockage, table_produits.ref_couleur, 
table_produits.sortie_de_collection, table_produits.top_vente, table_produits.coup_coeur, table_produits.vente_flash 
 
from table_produits 
rightt join table_prd_description on table_produits.prd_id = table_prd_description.prd_id 
right join table_prd_categories on table_produits.prd_id = table_prd_categories.prd_id
right join table_categories on table_prd_categories.cat_code = table_categories.code 
 
where 1 
and actif=1 
and cat_code REGEXP '^[0-9]{2}20' 
and cat_code REGEXP '^[0-9]{4}10' 
and cat_code REGEXP '^10|^30|^20' 
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (147,231) AND prd_id = table_produits.prd_id) 
and EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (83,199) AND prd_id = table_produits.prd_id) 
and NOT EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (308,309,310,311) AND prd_id = table_produits.prd_id) 
and NOT EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (17) AND prd_id = table_produits.prd_id) 
AND sortie_de_collection != 1 
and table_prd_categories.cat_code not like '00%' 
and prix_promo BETWEEN 0 and 1500 
 
GROUP BY reference 
order by table_produits.ventes DESC ,reference LIMIT 0,12

Et celle-ci quasi instantanée (la même sans les EXISTS):

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
select table_produits.prd_id, table_produits.reference, table_produits.prix_promo, table_produits.prix_ttc, 
table_prd_categories.cat_code, table_produits.nouveauter, table_produits.destockage, table_produits.ref_couleur, 
table_produits.sortie_de_collection, table_produits.top_vente, table_produits.coup_coeur, table_produits.vente_flash 
 
from table_produits 
rightt join table_prd_description on table_produits.prd_id = table_prd_description.prd_id 
right join table_prd_categories on table_produits.prd_id = table_prd_categories.prd_id
right join table_categories on table_prd_categories.cat_code = table_categories.code 
 
where 1 
and actif=1 
and cat_code REGEXP '^[0-9]{2}20' 
and cat_code REGEXP '^[0-9]{4}10' 
and cat_code REGEXP '^10|^30|^20' 
AND sortie_de_collection != 1 
and table_prd_categories.cat_code not like '00%' 
and prix_promo BETWEEN 0 and 1500 
 
GROUP BY reference 
order by table_produits.ventes DESC ,reference LIMIT 0,12

Le problème (le plus important) est clair, la table "table_prd_infos" est la cause de 90% de mes ralentissements.

Je n'up pas ce post sans avoir de nouvelles informations cela dit !
- On m'a conseillé de mettre des index sur les colonnes avec lesquels je travail le plus mes sélections, sauf que c'était déjà fait...
- Pour information, table_prd_infos contient pratiquement 2 millions d'enregistrements. On ma dit que ce n'était pas si grave.
- Lorsque j'exécute une des requêtes trop longues, la RAM du serveur n'est exploitée qu'à hauteur de 25% de ses capacités, alors que j'ai indexé tous les champs de cette table, juste pour voir...

Malheureusement, depuis que j'ai créé ce post, je ne vois vraiment pas comment modifier la façon dont je dois stocker les infos produits, malgré toute l'aide que vous m'avez apportez et je vous en suis très reconnaissant.
Je pense que j'ai tellement travaillé avec cette DB qu'elle me semble optimisé telle qu'elle est et je n'arrive pas à sortir de ce modèle.

Voilà donc où j'en suis... Au point de départ, malgré une belle requête toute neuve. Je vous implore une dernière fois, quelqu'un voit comment je pourrais stocker ces données autrement ? Il n'est plus question de ne pas changer la structure de la DB, mon responsable c'est fait à l'idée (enfin un point positif ! )

Amicalement, une âme perdue dans les méandres du sql...

[punkoff]

Vous avez les plans d'execution des requetes ?

[Kalmani]

Bonjour,

C'est bien un Explain de la requête que vous demandez ?

Si oui, voici le résultat que j'obtiens sous PhpMyAdmin :


(j'ai juste masqué les préfixes et nom de la db)

[aieeeuuuuu]

Mon problème est toujours le même. Voici une requête qui met environ 10 secondes à s'exécuter :

Que donne cette requête après avoir placé cet index

Code :

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

CREATE [UNIQUE] INDEX IX_TEST ON table_prd_infos (prd_id , champs_options_id )