Calcul de combinaisons possibles de parametres

**Kalmani** · 11/12/2013, 17h52

Bonjour tout le monde !

J'ai précédemment posté pour un problème d'optimisation de requête MySQL qui c'est soldé par un très beau succès !

Si je reviens vous voir, c'est pour encore pousser cette optimisation (en l’occurrence, le temps de chargement du site qui est du en grande partie à des requêtes trop lourdes.

NB : Non, je ne me suis pas trompé de forum, du moins je crois ! Mais ce post contient des notions de PHP et d'algorithmie, donc je ne savais pas trop où le placer si ce n'est ici

Présentation du problème :
Actuellement, sur le site de ma société, nous avons un listing produits lent dans un premier temps et rapide dès le 2ème ajout d'un filtre de recherche. Cette rapidité s'explique par le fait que je transmet une série d'IDs de produits qui correspondront forcement à l'ajout d'un nouveau filtre. J'inclus donc ces IDS dans la clause WHERE de ma requête ( where prd_id IN (.....) ).
Ce type de requête met entre 0.10 et 0.20 secondes donc aucun problème.

Seulement voilà, les premières requêtes, sans IDs pré sélectionnés, mettent au bas mot 5 à 10 secondes !
Avant de me le dire, oui l'optimisation de la table qui stock les produits est en cours.

Objectif :
J'aimerais donc, si c'est possible (mais je suis sûr que ça l'est :p), envisager de stocker les séries d'IDs pour chaque combinaison de filtres !

Voici comment je compte stocker tous les filtres pour le script que je tente de créer :
Un array, chaque clé = nom du filtre (exemple "couleur", ou "matière").
Chaque cellule du tableau contient un autre tableau avec les différentes valeurs possibles (exemple "rouge", "vert", ...).

Sur la base de ce tableau multi dimensionnel, je souhaite donc créer un script qui me permettrais de calculer chaque possibilité de combinaisons, allant de 1 à n paramètres combinés, en évitant les doublons.

Je n'ai malheureusement pas encore d'exemple concret de tentative à vous exposer. Je voulais d'abord savoir si c'était possible, ou si on était trop dans ma petite tête :p

Par avance merci pour votre lecture, et désolé pour le pavé

**sabotage** · 11/12/2013, 18h02

Difficile de te suivre.
Comment passes-tu de "couleur-vert" à prd_id IN (...) ?

**Kalmani** · 11/12/2013, 18h12

Ça, c'est un autre problème que je sais résoudre

Mais en gros, dans ce qui existe déjà, les IDs sont récupérés en même temps que la génération du listing produit

Pour ce qui est du problème actuel, je compte effectuer une requête sélective en fonction des combinaisons de paramètres, ce qui me donnera les IDs.

L'objectif final est de stocker ces IDs dans une table à part, ou un .csv, ou peut importe... afin de les récupérer quand la recherche effectuée par le client correspond aux paramètres dont j'ai parlé précédemment.

Tout ceci pour alléger la requête!

**sabotage** · 11/12/2013, 18h27

Je ne vois pas bien ou tu veux avec cette liste de combinaisons mais pour le faire il faut que tu passes par une fonction recursive.

**mlebeguec** · 11/12/2013, 20h29

Bonsoir,

Tu as combien de produits pour que ce soit si lent?

Lors de l'accès à la page, par défaut il y a la liste de tous les produits ensuite ces produits sont filtrés fonction de la recherche du client ?

C'est ça ?

Natso

**Kalmani** · 12/12/2013, 10h50

Bonjour,

Envoyé par sabotage

Je ne vois pas bien ou tu veux avec cette liste de combinaisons mais pour le faire il faut que tu passes par une fonction recursive.

Je souhaite générer toutes les combinaisons possibles pour en retirer les produits concernés, c'est tout

Ca me permettra d'effectuer des requêtes plus sélectives et donc plus rapides

Envoyé par mlebeguec

Bonsoir,

Tu as combien de produits pour que ce soit si lent?

Lors de l'accès à la page, par défaut il y a la liste de tous les produits ensuite ces produits sont filtrés fonction de la recherche du client ?

C'est ça ?

Natso

7 513 produits sur une table assez large. Je sais que ce n'est pas énorme mais je soupçonne notre serveur d'être un peu mou. Avant d'envisager de passer sur une gamme supérieur on souhaitait explorer cette technique de pré sélection.

Les produits sont filtrés à la sources, dans la requête SQL. C'est celle ci qui est extrêmement lente !

**Kalmani** · 12/12/2013, 11h37

Bon j'illustre un peu pour Sabotage

Ici une requête qui peut prendre jusqu'à 10 secondes de traitement (quand même...)

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
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_categories on table_prd_categories.cat_code = table_categories.code 
 
where 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 (197) 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 cat_code not like '00%' 
and prix_promo BETWEEN 0 and 1500 
 
GROUP BY reference 
order by ventes DESC ,reference 
LIMIT 0,12

Et là la même requête qui prend entre 0.10 et 0.20 secondes...

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
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_categories on table_prd_categories.cat_code = table_categories.code 
 
where actif=1 
and prd_id IN (6618,6973,6974,296,317,323,328,331,335,337,364,365,377,379,383,384,386,387,388,390,392,6969,6998,906,907,909,910,915,1564,1624,1626,1629,6975,2314,2315,2319,2321,2322,2413,2416,2435,2531,2535,2547,2643,2681,2720,2722,2732,2735,2858,2859,2860,2959,2964,2966,6980,6510,6507,6506,6505,6502,6994,6983,6984,5907,5937,5938,5939,5940,5941,6978,6224,6415,6416,6620,6623,6659,6660,6661,6662,6663,6664,6665,6668,6672,6673,6982,6908,6909,6981,7144,7145,7146,7147,7188,7189,7190,7191,7195,7258,7259,7260,7315,7355,7436,7438,7490,7497,7540,7544,7545,7546,7547,7548,7557,7558,7559,7560,7582,7583,7584,7585,7598,7607,7608,7609,7610,7621,7632,7633,7634,7635,7644,7645,7646,7657,7658,7659,7675,7898,8235,8236,8237,8238,8239,8240,8241) 
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 (197) 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 cat_code not like '00%' 
and prix_promo BETWEEN 0 and 1500 
 
GROUP BY reference 
order by ventes DESC ,reference 
LIMIT 0,12

Voilà où je veux en venir, mais dès la première requête. Car cette série d'IDs, je l’obtient en fonction des paramètres précédemment sélectionnés par le client.

Autre problème technique, le traitement passe en ajax, or quand il y a trop d'ids à transmettre via je javascript, il considère que l'url est trop longue, donc dans certains cas je ne transfert même pas les IDs. D'où l’intérêt d'avoir un système qui met en cache toutes les possibilités de combinaisons de filtres, afin d'avoir les séries d'IDs pour chacune de ces combinaisons !

Edit: Je ne voulais pas trop en venir à citer un exemple de requête car j'avais peur que la discussion dérive sur ce sujet alors que ce n'est pas là le problème, du moins je pense.

Edit2: J'ai pris le temps de vous faire un petit tableau de tous les filtres et de leurs valeurs si jamais ça peut aider à la compréhension.

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
<?php
 
$params = array(
	'type'=>array(10, 20),
	'genre'=>array(10, 20, 40, 50),
	'style'=>array(44, 47, 308, 309, 310, 311),
	'poly'=>array(1),
	'sol_sport'=>array(1),
	'destock'=>array(1),
	'formVer'=>array(81, 82, 83, 84, 196, 197, 198, 199, 226, 251),
	'colMont'=>array(20, 147, 148, 149, 151, 152, 153, 229, 233, 238, 247, 272),
	'uni'=>array(6, 7, 9, 235),
	'degra'=>array(6, 7, 9, 235),
	'miroir'=>array(16, 102, 292, 295, 318, 331, 359),
	'cambre'=>array(86),
	'mat'=>array(26, 28, 29),
	'tMont'=>array(48, 49, 50),
	'lBran'=>array(181, 180, 179),
	'decor'=>array(52, 53, 54),
	'vue'=>array(88),
	'traiVer'=>array(18, 17, 90, 13)
);
 
?>

**sabotage** · 12/12/2013, 12h36

Je ne comprends vraiment pas ce que tu fais.

Si je veux tous les produits rouge je fais

Code sql :

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

SELECT * FROM produits WHERE couleur = id_correspondant_à_rouge

**Kalmani** · 12/12/2013, 12h40

^^'

Moi aussi c'est ce que je fais ce n'est pas là le problème

Compares mes deux requêtes, l'une met 10 secondes à être traitée, l'autre 0.10 secondes. La secondes contient une clause supplémentaire pour filtrer par IDs.

Alors que la différence de temps du traitement soit si importante je ne me l'explique pas, mais c'est un fait.

L'objectif est DONC d'avoir une pré sélection d'IDs produits quelque soit les paramètres choisis par le client !
Après ça, je ne vois pas comment expliquer plus clairement le problème

Pour info, les paramètres des produits ne sont pas stockés dans la même table, mais dans une table immense qui contient tous les paramètres de tous les produits. C'est peut être là une des raisons de cette lenteur.

Bien.... On parle, on parle, mais je me rend compte d'une chose: ce que je demande n'a aucune différence avec le faite de mettre en cache les résultats à afficher, tout simplement.
Car quitte à avoir les IDs des produits concernés, autant avoir tout le résultat en cache et les requêtes en front n'auraient plus aucun intérêt...

Du coup le problème est tout autre et se pose surtout sur la structure de la table qui stock les paramètres des produits...

Au moins j'ai appris une chose, c'est que j'ai beaucoup de mal à retranscrire par écrit ce que j'ai dans la tête

**sabotage** · 12/12/2013, 13h34

Alors que la différence de temps du traitement soit si importante je ne me l'explique pas, mais c'est un fait.

Demander toutes les entrées ou seulement quelques ids ça fait quand même un grosse différence.

Mes questions ont un sens, tu sembles monter un vraie grosse usine à gaz.
Par exemple pourquoi

Code sql :

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

1
2
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 (197) AND prd_id = table_produits.prd_id)

et pas simplement

Code sql :

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

AND EXISTS (SELECT * FROM table_prd_infos WHERE champs_options_id IN (147,231,197) AND prd_id = table_produits.prd_id)

?

**Kalmani** · 12/12/2013, 14h02

Car si tu regarde mon tableau de paramètres, les valeurs 147 et 231 (bleu et rouge par exemple) correspondent au paramètre "couleur" et 197 au paramètre "forme" (rond)

En français, ces conditions correspondent à "produit (bleu ou rouge) ET rond",
donc en gros, produit rond bleu OU rond rouge.

La méthode n'est pas bonne ?

**mlebeguec** · 12/12/2013, 19h05

Bonsoir,

Je n'ai pas regardé les requêtes car pour moi c'est imbitable.

Tu peux créer une vue.

Natso

**sabotage** · 12/12/2013, 21h37

Ton problème est toujours le même : ta structure est bancale ce qui donne des requêtes monstrueuses et tu es maintenant en train de mettre en place des contournements délirants.

**Kalmani** · 13/12/2013, 12h43

Bonjour,

Plus le temps passe et plus je me dis que c'est effectivement la seule chose à faire... changer toute la structure...

Pour ce qui est de la vue, je passe peut être pour le dernier des branques mais comment je peux en faire une facilement ?

**Dendrite** · 13/12/2013, 16h03

Quand mes requêtes ou mes vues sont lentes... parfois, c'est simplement que j'ai oublié d'indexer un champ indexable... On enlève des champs, on rajoute des champs, et parfois, on en oublie les clés.
Une vue, c'est une requête, un peu figée en table pour la présentation (interface graphique). Ce qui fait que si tu requêtes dans ta vue, c'est comme si tu faisais une requête imbriquée.
Pour les temps d'éxécution, ça n'optimise rien du tout. C'est surtout plus intuitif pour les humains.
La syntaxe est toute bête en mysql

Code sql :

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

1
2
3
4
create or replace view v_toto as 
select blablabla 
from blablabla
where blablabla

**Kalmani** · 13/12/2013, 16h14

Merci pour l'info

Pour ce qui est des indexations, elles sont faites

Je penses que c'est vraiment la façon dont les tables ont été structurées qui fait que les requêtes soient si lente au final.

Calcul de combinaisons possibles de parametres

Langage PHP

Discussions similaires

Partager

Partager