Voici mon problème j'ai deux table que j' essaie de joindre ensemble pour gagner en temps d'execution
Une table products_description :
Colonne Type Null Défaut Commentaires
products_id int(11) Non
language_id int(11) Non 1
products_name varchar(128) Non
products_description text Oui NULL
products_url varchar(255) Oui NULL
products_viewed int(5) Oui 0
extra_value_id2 int(10) Non 0
extra_value_id3 int(10) Non 0
extra_value_id4 int(10) Non 0
extra_value_id5 int(10) Non 0
extra_value_id6 int(10) Non 0
extra_value_id8 int(10) Non 0
extra_value_id9 int(10) Non 0
ET une extra_field_values :
Colonne Type Null Défaut Commentaires
value_id int(10) Non
epf_id int(10) Non
languages_id int(11) Non
parent_id int(10) Non 0
sort_order int(11) Non 0
epf_value varchar(64) Oui NULL
Je dois joindre les deux table pour que ce qui est contenu dans products_description.extra_value_id[0-9] et que je retrouve dans extra_field_values.epf_id correspondent à la description textuelle contenue dans extra_field_values.epf_value (Pas facile a expliquer !!)
En gros une jointure qui fonctionne donne ceci :
SELECT DISTINCT pd.products_id, epf.epf_value from products_description pd left join extra_field_values epf on (pd.extra_value_id3 = epf.value_id)
Sauf que je suis obligé de faire 7 jointure rien que lire tout les infos qui m'intéresse et sur une base de données de 10000 articles çà met une éternité même avec des index.
DOnc ma question est puis-je faire qqchose qui serait du genre avec une regex ou autre :
SELECT DISTINCT pd.products_id, epf.epf_value from products_description pd left join extra_field_values epf on (pd.extra_value_id[0-9] = epf.value_id)
Ou si qq1 à une idée de requête optimisée qui m'éviterais 45s de chargement ...
Ce qu'il faudrait surtout, à mon humble avis, c'est enlever ces moches colonnes extra_value_idX et faire une table intermédiaire avec 2 colonnes : product_desc_id et extra_value_id.
Ce qu'il faudrait surtout, à mon humble avis, c'est enlever ces moches colonnes extra_value_idX et faire une table intermédiaire avec 2 colonnes : product_desc_id et extra_value_id.
Peux tu me donner plus de précision sur ton idée car chaque extra_value_idX correspond a un attribut produit différent ( couleur, taille ..)
Oui je sais bien que celà aurait été la solution idéale mais à la base la conception de cette base n'est pas de mon ressort mais celle d'un prestataire basée sur un CMS e-commerce et j'aimerais éviter de tout refaire, d'ou ma question pour optimiser la requête en limitant les jointure, tu penses qu'en l'état c'est impossible ?
> Sauf que je suis obligé de faire 7 jointure rien que lire
> tout les infos qui m'intéresse et sur une base de données
> de 10000 articles çà met une éternité même avec des index.
Y a quand même pas de raison que ça prenne une éternité. Envoie les définitions des 2 tables (SHOW CREATE TABLE), ta monster-requête et un EXPLAIN de ta monster-requête.
La requête complète, d'autres tables sont jointes par rapport à ma demande initiale, je voulais y aller progressivement pour l'alléger :
SELECT SQL_CALC_FOUND_ROWS DISTINCT epf.epf_value, epf.sort_order, epf.epf_id , epf.value_id FROM products p LEFT JOIN products_description pd on (p.products_id = pd.products_id) LEFT JOIN products_to_categories p2c on (p.products_id = p2c.products_id) LEFT JOIN categories c on (c.categories_id = p2c.categories_id) left join categories_description as cd on (c.categories_id = cd.categories_id and cd.language_id = "4") LEFT JOIN extra_field_values AS epf ON (epf.value_id = extra_value_id2 or epf.value_id = extra_value_id3 or epf.value_id = extra_value_id4 or epf.value_id = extra_value_id5 or epf.value_id = extra_value_id6 or epf.value_id = extra_value_id8 or epf.value_id = extra_value_id9 ) and (epf.languages_id = "4") WHERE (p2c.categories_id = "89915" or p2c.categories_id = "89916" or p2c.categories_id = "89917" or p2c.categories_id = "89936" or p2c.categories_id = "89958" or p2c.categories_id = "92107") AND p.products_status = "1" AND p.vendu = "0" AND pd.language_id = "4" GROUP BY epf.epf_value ORDER BY epf.sort_order, epf.epf_value
Le Explain :
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE s system NULL NULL NULL NULL 0 const row not found
1 SIMPLE p2c range PRIMARY,products_id,categories_id categories_id 4 NULL 79 Using where; Using temporary; Using filesort
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 laurent.p2c.categories_id 1 Using index
1 SIMPLE p eq_ref PRIMARY,products_status PRIMARY 4 laurent.p2c.products_id 1 Using where
1 SIMPLE cd eq_ref PRIMARY PRIMARY 8 laurent.c.categories_id,const 1 Using index
1 SIMPLE pd eq_ref PRIMARY,language_id PRIMARY 8 laurent.p2c.products_id,const 1 Using where
1 SIMPLE epf ALL PRIMARY NULL NULL NULL 6499
Tu utilises des LEFT JOIN partout, t'es certain d'en avoir réellement besoin ?
Ensuite remplace tes OR par des IN ( ... )
Essaie comme ça:
SELECT SQL_CALC_FOUND_ROWS
DISTINCT epf.epf_value, epf.sort_order, epf.epf_id , epf.value_id
FROM products p
INNER JOIN products_to_categories p2c
ON p.products_id = p2c.products_id
AND p.products_status = 1
AND p.vendu = 0
AND p2c.categories_id IN (89915, 89916, 89917, 89936, 89958, 92107)
INNER JOIN categories c
ON c.categories_id = p2c.categories_id
LEFT JOIN categories_description AS cd
ON c.categories_id = cd.categories_id
AND cd.language_id = 4
LEFT JOIN extra_field_values AS epf
ON epf.value_id IN (
p.extra_value_id2, p.extra_value_id3, p.extra_value_id4, p.extra_value_id5,
p.extra_value_id6, extra_value_id8 OR epf.value_id = extra_value_id9
)
AND epf.languages_id = 4
LEFT JOIN products_description pd
ON p.products_id = pd.products_id
AND pd.language_id = 4
-- GROUP BY epf.epf_value ?? pourquoi ce group by
ORDER BY epf.sort_order, epf.epf_value
[ed] J'ai modifié en ajoutant quelque LEFT JOIN aux endroits où on peut imaginer qu'il n'y a pas de traduction correspondant à l'id choisi. à voir. Sur extra_field_values c'est un peu étrange vu que c'est ce que tu cherches à remonter, mais pourquoi pas..
on pourrait virer pas mal de tables de cette requête.
SQL_CALC_FOUND_ROWS ne sert à rien puisqu'on n'a pas de LIMIT.
Le GROUP BY est effectivement douteux.
SELECT
DISTINCT epf.epf_value, epf.sort_order, epf.epf_id , epf.value_id
FROM products p
JOIN products_to_categories p2c
ON p.products_id = p2c.products_id
AND p.products_status = 1
AND p.vendu = 0
AND p2c.categories_id IN (89915, 89916, 89917, 89936, 89958, 92107)
JOIN extra_field_values AS epf
ON epf.value_id IN (
p.extra_value_id2,
p.extra_value_id3,
p.extra_value_id4,
p.extra_value_id5,
p.extra_value_id6,
p.extra_value_id8,
p.extra_value_id9
)
AND epf.languages_id = 4
ORDER BY epf.sort_order, epf.epf_value
Effectivement le coup des colonnes extra_values_machin numérotées c'est atroce, le prestataire qui a pondu cette merde mérite le fouet, et je suppute aussi que c'est son code qui a généré cette monstruosité que tu m'as envoyé par PM :
SELECT SQL_CALC_FOUND_ROWS DISTINCT epf.epf_value, epf.sort_order, epf.epf_id , epf.value_id FROM products_description pd LEFT JOIN products_to_categories p2c on (pd.products_id = p2c.products_id) LEFT JOIN categories c on (c.categories_id = p2c.categories_id) LEFT JOIN extra_field_values AS epf ON (epf.value_id = extra_value_id2 or epf.value_id = extra_value_id3 or epf.value_id = extra_value_id4 or epf.value_id = extra_value_id5 or epf.value_id = extra_value_id6 or epf.value_id = extra_value_id8 or epf.value_id = extra_value_id9 ) and (epf.languages_id = "4") WHERE (p2c.categories_id = "91041" or p2c.categories_id = "93903" or p2c.categories_id = "93904" or p2c.categories_id = "93905" or p2c.categories_id = "93906" or p2c.categories_id = "93907" or p2c.categories_id = "93908" or p2c.categories_id = "91042" or p2c.categories_id = "91043" or p2c.categories_id = "91044" or p2c.categories_id = "91045" or p2c.categories_id = "91046" or p2c.categories_id = "91047" or p2c.categories_id = "91048" or p2c.categories_id = "91055" or p2c.categories_id = "91056" or p2c.categories_id = "91057" or p2c.categories_id = "91060" or p2c.categories_id = "91065" or p2c.categories_id = "91068" or p2c.categories_id = "91069" or p2c.categories_id = "91070" or p2c.categories_id = "92238" or p2c.categories_id = "92243" or p2c.categories_id = "92244" or p2c.categories_id = "91071" or p2c.categories_id = "92703" or p2c.categories_id = "91072" or p2c.categories_id = "91073" or p2c.categories_id = "91074" or p2c.categories_id = "91078" or p2c.categories_id = "91082" or p2c.categories_id = "91084" or p2c.categories_id = "91087" or p2c.categories_id = "93998" or p2c.categories_id = "93999" or p2c.categories_id = "94000" or p2c.categories_id = "94001" or p2c.categories_id = "94002" or p2c.categories_id = "94003" or p2c.categories_id = "94004" or p2c.categories_id = "94005" or p2c.categories_id = "91088" or p2c.categories_id = "91089" or p2c.categories_id = "91092" or p2c.categories_id = "91094" or p2c.categories_id = "91095" or p2c.categories_id = "91096" or p2c.categories_id = "91098" or p2c.categories_id = "91099" or p2c.categories_id = "91100" or p2c.categories_id = "91105" or p2c.categories_id = "91106" or p2c.categories_id = "91107" or p2c.categories_id = "91108" or p2c.categories_id = "91109" or p2c.categories_id = "91110" or p2c.categories_id = "91112" or p2c.categories_id = "91113" or p2c.categories_id = "91114" or p2c.categories_id = "91115" or p2c.categories_id = "91116" or p2c.categories_id = "91117" or p2c.categories_id = "91118" or p2c.categories_id = "91121" or p2c.categories_id = "91126" or p2c.categories_id = "91127" or p2c.categories_id = "91128" or p2c.categories_id = "91129" or p2c.categories_id = "91132" or p2c.categories_id = "91134" or p2c.categories_id = "91135" or p2c.categories_id = "91136" or p2c.categories_id = "91137" or p2c.categories_id = "91143" or p2c.categories_id = "91146" or p2c.categories_id = "91147" or p2c.categories_id = "91152" or p2c.categories_id = "91159" or p2c.categories_id = "91160" or p2c.categories_id = "91161" or p2c.categories_id = "91163" or p2c.categories_id = "91164" or p2c.categories_id = "91166" or p2c.categories_id = "91168" or p2c.categories_id = "91169" or p2c.categories_id = "91171" or p2c.categories_id = "91174" or p2c.categories_id = "91176" or p2c.categories_id = "91177" or p2c.categories_id = "92774" or p2c.categories_id = "92775" or p2c.categories_id = "92779" or p2c.categories_id = "92792" or p2c.categories_id = "92797" or p2c.categories_id = "92798" or p2c.categories_id = "92802" or p2c.categories_id = "92803" or p2c.categories_id = "92805" or p2c.categories_id = "92806" or p2c.categories_id = "92807" or p2c.categories_id = "92808" or p2c.categories_id = "92809" or p2c.categories_id = "92810" or p2c.categories_id = "91178" or p2c.categories_id = "91179" or p2c.categories_id = "91180" or p2c.categories_id = "91182" or p2c.categories_id = "91183" or p2c.categories_id = "91185" or p2c.categories_id = "91186" or p2c.categories_id = "92266" or p2c.categories_id = "92267" or p2c.categories_id = "92268" or p2c.categories_id = "92269" or p2c.categories_id = "92271" or p2c.categories_id = "92278" or p2c.categories_id = "92284" or p2c.categories_id = "92285" or p2c.categories_id = "92286" or p2c.categories_id = "92287" or p2c.categories_id = "92288" or p2c.categories_id = "92292" or p2c.categories_id = "92294" or p2c.categories_id = "92300" or p2c.categories_id = "91187" or p2c.categories_id = "91189" or p2c.categories_id = "91190" or p2c.categories_id = "91193" or p2c.categories_id = "91197" or p2c.categories_id = "92103" or p2c.categories_id = "92310" or p2c.categories_id = "92312" or p2c.categories_id = "92322" or p2c.categories_id = "91198" or p2c.categories_id = "91200" or p2c.categories_id = "91202" or p2c.categories_id = "91203" or p2c.categories_id = "91204" or p2c.categories_id = "91205" or p2c.categories_id = "91206" or p2c.categories_id = "91207" or p2c.categories_id = "91208" or p2c.categories_id = "91705" or p2c.categories_id = "91209" or p2c.categories_id = "91210" or p2c.categories_id = "91211" or p2c.categories_id = "91212" or p2c.categories_id = "91215" or p2c.categories_id = "92563" or p2c.categories_id = "92565" or p2c.categories_id = "92566" or p2c.categories_id = "92567" or p2c.categories_id = "91216" or p2c.categories_id = "91217" or p2c.categories_id = "91218" or p2c.categories_id = "91219" or p2c.categories_id = "92397" or p2c.categories_id = "92398" or p2c.categories_id = "92400" or p2c.categories_id = "91220" or p2c.categories_id = "93902" or p2c.categories_id = "91221" or p2c.categories_id = "91223" or p2c.categories_id = "91224" or p2c.categories_id = "91225" or p2c.categories_id = "91226" or p2c.categories_id = "91227" or p2c.categories_id = "91228" or p2c.categories_id = "93797" or p2c.categories_id = "93985" or p2c.categories_id = "93993" or p2c.categories_id = "93994" or p2c.categories_id = "93995" or p2c.categories_id = "94036" or p2c.categories_id = "94037" or p2c.categories_id = "94038" or p2c.categories_id = "91229" or p2c.categories_id = "91230" or p2c.categories_id = "91231" or p2c.categories_id = "91232" or p2c.categories_id = "91233" or p2c.categories_id = "91234" or p2c.categories_id = "91236" or p2c.categories_id = "91237" or p2c.categories_id = "91238" or p2c.categories_id = "91239" or p2c.categories_id = "91241" or p2c.categories_id = "91242" or p2c.categories_id = "91246" or p2c.categories_id = "91247" or p2c.categories_id = "91248" or p2c.categories_id = "91252" or p2c.categories_id = "91253" or p2c.categories_id = "91254" or p2c.categories_id = "91255" or p2c.categories_id = "91256" or p2c.categories_id = "91260" or p2c.categories_id = "91262" or p2c.categories_id = "91263" or p2c.categories_id = "92621" or p2c.categories_id = "92622" or p2c.categories_id = "92623" or p2c.categories_id = "92625" or p2c.categories_id = "92626" or p2c.categories_id = "92631" or p2c.categories_id = "92632" or p2c.categories_id = "92633" or p2c.categories_id = "92634" or p2c.categories_id = "92635" or p2c.categories_id = "91265" or p2c.categories_id = "92706" or p2c.categories_id = "91266" or p2c.categories_id = "92404" or p2c.categories_id = "92407" or p2c.categories_id = "92408" or p2c.categories_id = "91267" or p2c.categories_id = "91268" or p2c.categories_id = "91271" or p2c.categories_id = "92105" or p2c.categories_id = "92825" or p2c.categories_id = "92834" or p2c.categories_id = "92835" or p2c.categories_id = "92836" or p2c.categories_id = "92839" or p2c.categories_id = "92840" or p2c.categories_id = "92844" or p2c.categories_id = "92849" or p2c.categories_id = "92855" or p2c.categories_id = "92856" or p2c.categories_id = "92859" or p2c.categories_id = "91274" or p2c.categories_id = "91275" or p2c.categories_id = "91276" or p2c.categories_id = "91277" or p2c.categories_id = "91278" or p2c.categories_id = "91279" or p2c.categories_id = "91280" or p2c.categories_id = "91282" or p2c.categories_id = "91283" or p2c.categories_id = "91284" or p2c.categories_id = "91285" or p2c.categories_id = "93234" or p2c.categories_id = "93235" or p2c.categories_id = "93236" or p2c.categories_id = "93237" or p2c.categories_id = "91286" or p2c.categories_id = "92865" or p2c.categories_id = "92866" or p2c.categories_id = "92867" or p2c.categories_id = "92869" or p2c.categories_id = "92870" or p2c.categories_id = "92873" or p2c.categories_id = "92875" or p2c.categories_id = "92882" or p2c.categories_id = "92889" or p2c.categories_id = "91538" or p2c.categories_id = "91539" or p2c.categories_id = "91540" or p2c.categories_id = "91541" or p2c.categories_id = "91542" or p2c.categories_id = "92249" or p2c.categories_id = "92252" or p2c.categories_id = "92253" or p2c.categories_id = "92254" or p2c.categories_id = "92255" or p2c.categories_id = "92257" or p2c.categories_id = "92258" or p2c.categories_id = "92263" or p2c.categories_id = "92264" or p2c.categories_id = "92265" or p2c.categories_id = "91545" or p2c.categories_id = "92893" or p2c.categories_id = "92895" or p2c.categories_id = "92897" or p2c.categories_id = "92899" or p2c.categories_id = "92901" or p2c.categories_id = "92902" or p2c.categories_id = "92905" or p2c.categories_id = "92906" or p2c.categories_id = "92909" or p2c.categories_id = "92910" or p2c.categories_id = "92911" or p2c.categories_id = "92912" or p2c.categories_id = "92913" or p2c.categories_id = "92914" or p2c.categories_id = "92915" or p2c.categories_id = "92916" or p2c.categories_id = "92917" or p2c.categories_id = "92918" or p2c.categories_id = "92920" or p2c.categories_id = "92922" or p2c.categories_id = "92923" or p2c.categories_id = "92924" or p2c.categories_id = "92925" or p2c.categories_id = "92926" or p2c.categories_id = "92940" or p2c.categories_id = "91547" or p2c.categories_id = "92944" or p2c.categories_id = "92946" or p2c.categories_id = "92947" or p2c.categories_id = "91549" or p2c.categories_id = "91550" or p2c.categories_id = "91551" or p2c.categories_id = "91552" or p2c.categories_id = "91553" or p2c.categories_id = "91554" or p2c.categories_id = "91555" or p2c.categories_id = "91557" or p2c.categories_id = "91558" or p2c.categories_id = "91559" or p2c.categories_id = "91561" or p2c.categories_id = "91562" or p2c.categories_id = "91563" or p2c.categories_id = "91564" or p2c.categories_id = "91565" or p2c.categories_id = "91566" or p2c.categories_id = "91567" or p2c.categories_id = "91568" or p2c.categories_id = "91569" or p2c.categories_id = "91570" or p2c.categories_id = "91571" or p2c.categories_id = "91572" or p2c.categories_id = "91573" or p2c.categories_id = "91574" or p2c.categories_id = "93812" or p2c.categories_id = "91575" or p2c.categories_id = "91576" or p2c.categories_id = "91577" or p2c.categories_id = "92677" or p2c.categories_id = "92679" or p2c.categories_id = "92680" or p2c.categories_id = "91578" or p2c.categories_id = "91580" or p2c.categories_id = "91581" or p2c.categories_id = "91582" or p2c.categories_id = "91583" or p2c.categories_id = "91585" or p2c.categories_id = "91587" or p2c.categories_id = "93005" or p2c.categories_id = "93006" or p2c.categories_id = "93008" or p2c.categories_id = "93009" or p2c.categories_id = "93010" or p2c.categories_id = "91593" or p2c.categories_id = "91595" or p2c.categories_id = "93015" or p2c.categories_id = "93020" or p2c.categories_id = "93022" or p2c.categories_id = "93023" or p2c.categories_id = "93026" or p2c.categories_id = "93034" or p2c.categories_id = "93035" or p2c.categories_id = "93041" or p2c.categories_id = "93042" or p2c.categories_id = "93044" or p2c.categories_id = "93046" or p2c.categories_id = "93048" or p2c.categories_id = "91596" or p2c.categories_id = "91597" or p2c.categories_id = "92687" or p2c.categories_id = "92688" or p2c.categories_id = "92689" or p2c.categories_id = "92690" or p2c.categories_id = "92691" or p2c.categories_id = "92692" or p2c.categories_id = "92693" or p2c.categories_id = "91598" or p2c.categories_id = "93049" or p2c.categories_id = "93051" or p2c.categories_id = "91602" or p2c.categories_id = "91603" or p2c.categories_id = "91604" or p2c.categories_id = "91605" or p2c.categories_id = "91606" or p2c.categories_id = "91607" or p2c.categories_id = "91608" or p2c.categories_id = "91609" or p2c.categories_id = "91610" or p2c.categories_id = "91611" or p2c.categories_id = "91612" or p2c.categories_id = "91613" or p2c.categories_id = "91614" or p2c.categories_id = "91615" or p2c.categories_id = "91616" or p2c.categories_id = "91617" or p2c.categories_id = "91618" or p2c.categories_id = "91619" or p2c.categories_id = "91620" or p2c.categories_id = "91621" or p2c.categories_id = "91622" or p2c.categories_id = "91623" or p2c.categories_id = "91624" or p2c.categories_id = "91625" or p2c.categories_id = "91626" or p2c.categories_id = "91627" or p2c.categories_id = "91628" or p2c.categories_id = "91629" or p2c.categories_id = "91631" or p2c.categories_id = "91632" or p2c.categories_id = "91633" or p2c.categories_id = "91634" or p2c.categories_id = "91635" or p2c.categories_id = "91636" or p2c.categories_id = "91638" or p2c.categories_id = "91639" or p2c.categories_id = "91640" or p2c.categories_id = "91641" or p2c.categories_id = "93059" or p2c.categories_id = "93060" or p2c.categories_id = "93061" or p2c.categories_id = "93062" or p2c.categories_id = "91642" or p2c.categories_id = "91643" or p2c.categories_id = "91644" or p2c.categories_id = "93840" or p2c.categories_id = "93839" or p2c.categories_id = "93838" or p2c.categories_id = "93837" or p2c.categories_id = "93836" or p2c.categories_id = "93842" or p2c.categories_id = "93841" or p2c.categories_id = "91645" or p2c.categories_id = "91646" or p2c.categories_id = "91647" or p2c.categories_id = "91648" or p2c.categories_id = "91649" or p2c.categories_id = "91650" or p2c.categories_id = "91652" or p2c.categories_id = "91654" or p2c.categories_id = "91655" or p2c.categories_id = "91656" or p2c.categories_id = "91664" or p2c.categories_id = "91670" or p2c.categories_id = "93065" or p2c.categories_id = "93066" or p2c.categories_id = "93067" or p2c.categories_id = "91671" or p2c.categories_id = "91672" or p2c.categories_id = "91673" or p2c.categories_id = "91674" or p2c.categories_id = "93860" or p2c.categories_id = "93856" or p2c.categories_id = "93859" or p2c.categories_id = "93858" or p2c.categories_id = "93857" or p2c.categories_id = "93855" or p2c.categories_id = "93854" or p2c.categories_id = "91675" or p2c.categories_id = "91676" or p2c.categories_id = "91677" or p2c.categories_id = "91678" or p2c.categories_id = "91679" or p2c.categories_id = "91680" or p2c.categories_id = "91681" or p2c.categories_id = "91682" or p2c.categories_id = "91683" or p2c.categories_id = "91684" or p2c.categories_id = "91685" or p2c.categories_id = "93079" or p2c.categories_id = "93080" or p2c.categories_id = "93085" or p2c.categories_id = "93086" or p2c.categories_id = "93087" or p2c.categories_id = "93088" or p2c.categories_id = "93089" or p2c.categories_id = "93091" or p2c.categories_id = "93092" or p2c.categories_id = "93093" or p2c.categories_id = "93094" or p2c.categories_id = "93095" or p2c.categories_id = "93096" or p2c.categories_id = "93097" or p2c.categories_id = "93098" or p2c.categories_id = "91687" or p2c.categories_id = "91688" or p2c.categories_id = "91689" or p2c.categories_id = "91690" or p2c.categories_id = "91691" or p2c.categories_id = "91692" or p2c.categories_id = "91693" or p2c.categories_id = "91697" or p2c.categories_id = "91699" or p2c.categories_id = "91700" or p2c.categories_id = "91707" or p2c.categories_id = "91708" or p2c.categories_id = "91709" or p2c.categories_id = "91710" or p2c.categories_id = "91711" or p2c.categories_id = "93873" or p2c.categories_id = "93871" or p2c.categories_id = "93872" or p2c.categories_id = "93869" or p2c.categories_id = "93867" or p2c.categories_id = "93870" or p2c.categories_id = "93868" or p2c.categories_id = "91712" or p2c.categories_id = "91713" or p2c.categories_id = "91715" or p2c.categories_id = "93127" or p2c.categories_id = "93129" or p2c.categories_id = "93132" or p2c.categories_id = "91716" or p2c.categories_id = "91717" or p2c.categories_id = "91723" or p2c.categories_id = "91724" or p2c.categories_id = "91725" or p2c.categories_id = "91726" or p2c.categories_id = "91727" or p2c.categories_id = "91729" or p2c.categories_id = "91731" or p2c.categories_id = "91733" or p2c.categories_id = "91734" or p2c.categories_id = "91736" or p2c.categories_id = "91737" or p2c.categories_id = "91738" or p2c.categories_id = "91739" or p2c.categories_id = "91740" or p2c.categories_id = "91748" or p2c.categories_id = "91776" or p2c.categories_id = "92409" or p2c.categories_id = "92410" or p2c.categories_id = "92412" or p2c.categories_id = "92413" or p2c.categories_id = "92414" or p2c.categories_id = "92415" or p2c.categories_id = "92416" or p2c.categories_id = "92420" or p2c.categories_id = "92422" or p2c.categories_id = "92423" or p2c.categories_id = "92434" or p2c.categories_id = "92437" or p2c.categories_id = "92439" or p2c.categories_id = "92440" or p2c.categories_id = "92441" or p2c.categories_id = "92442" or p2c.categories_id = "92443" or p2c.categories_id = "92444" or p2c.categories_id = "91777" or p2c.categories_id = "91778" or p2c.categories_id = "92448" or p2c.categories_id = "92452" or p2c.categories_id = "92455" or p2c.categories_id = "92458" or p2c.categories_id = "92459" or p2c.categories_id = "92460" or p2c.categories_id = "92465" or p2c.categories_id = "91779" or p2c.categories_id = "91784" or p2c.categories_id = "92468" or p2c.categories_id = "92469" or p2c.categories_id = "92470" or p2c.categories_id = "92472" or p2c.categories_id = "92473" or p2c.categories_id = "92474" or p2c.categories_id = "92475" or p2c.categories_id = "92476" or p2c.categories_id = "92479" or p2c.categories_id = "92481" or p2c.categories_id = "92483" or p2c.categories_id = "92484" or p2c.categories_id = "92487" or p2c.categories_id = "92491" or p2c.categories_id = "92496" or p2c.categories_id = "92499" or p2c.categories_id = "92505" or p2c.categories_id = "93843" or p2c.categories_id = "93845" or p2c.categories_id = "93844" or p2c.categories_id = "93848" or p2c.categories_id = "93847" or p2c.categories_id = "93849" or p2c.categories_id = "93846" or p2c.categories_id = "91786" or p2c.categories_id = "91787" or p2c.categories_id = "91788" or p2c.categories_id = "91789" or p2c.categories_id = "91790" or p2c.categories_id = "91791" or p2c.categories_id = "91792" or p2c.categories_id = "91793" or p2c.categories_id = "91795" or p2c.categories_id = "91796" or p2c.categories_id = "91797" or p2c.categories_id = "91798" or p2c.categories_id = "91799" or p2c.categories_id = "91800" or p2c.categories_id = "91801" or p2c.categories_id = "91806" or p2c.categories_id = "91807" or p2c.categories_id = "91814" or p2c.categories_id = "91817" or p2c.categories_id = "92508" or p2c.categories_id = "92509" or p2c.categories_id = "92512" or p2c.categories_id = "93821" or p2c.categories_id = "93822" or p2c.categories_id = "93824" or p2c.categories_id = "93823" or p2c.categories_id = "93825" or p2c.categories_id = "91819" or p2c.categories_id = "91820" or p2c.categories_id = "91821" or p2c.categories_id = "91823" or p2c.categories_id = "91824" or p2c.categories_id = "91827" or p2c.categories_id = "93814" or p2c.categories_id = "93816" or p2c.categories_id = "93815" or p2c.categories_id = "93820" or p2c.categories_id = "93819" or p2c.categories_id = "93818" or p2c.categories_id = "93817" or p2c.categories_id = "91835" or p2c.categories_id = "91836" or p2c.categories_id = "91837" or p2c.categories_id = "92519" or p2c.categories_id = "92520" or p2c.categories_id = "92521" or p2c.categories_id = "92522" or p2c.categories_id = "92523" or p2c.categories_id = "92532" or p2c.categories_id = "92534" or p2c.categories_id = "92536" or p2c.categories_id = "92537" or p2c.categories_id = "93784" or p2c.categories_id = "93787" or p2c.categories_id = "93786" or p2c.categories_id = "93785" or p2c.categories_id = "91839" or p2c.categories_id = "91840" or p2c.categories_id = "91842" or p2c.categories_id = "91843" or p2c.categories_id = "91844" or p2c.categories_id = "91846" or p2c.categories_id = "91847" or p2c.categories_id = "92549" or p2c.categories_id = "92551" or p2c.categories_id = "91848" or p2c.categories_id = "91849" or p2c.categories_id = "91851" or p2c.categories_id = "91856" or p2c.categories_id = "91857" or p2c.categories_id = "93980" or p2c.categories_id = "93981" or p2c.categories_id = "93982" or p2c.categories_id = "93983" or p2c.categories_id = "93984" or p2c.categories_id = "94006" or p2c.categories_id = "93798" or p2c.categories_id = "93799" or p2c.categories_id = "93804" or p2c.categories_id = "93803" or p2c.categories_id = "93802" or p2c.categories_id = "93801" or p2c.categories_id = "93800" or p2c.categories_id = "91860" or p2c.categories_id = "91861" or p2c.categories_id = "91862" or p2c.categories_id = "91863" or p2c.categories_id = "91864" or p2c.categories_id = "91865" or p2c.categories_id = "91866" or p2c.categories_id = "91867" or p2c.categories_id = "91871" or p2c.categories_id = "91872" or p2c.categories_id = "91873" or p2c.categories_id = "91876" or p2c.categories_id = "91877" or p2c.categories_id = "91878" or p2c.categories_id = "91879" or p2c.categories_id = "91880" or p2c.categories_id = "91881" or p2c.categories_id = "91882" or p2c.categories_id = "91883" or p2c.categories_id = "91884" or p2c.categories_id = "91885" or p2c.categories_id = "91886" or p2c.categories_id = "91887" or p2c.categories_id = "91888" or p2c.categories_id = "91889" or p2c.categories_id = "91890" or p2c.categories_id = "91891" or p2c.categories_id = "91892" or p2c.categories_id = "91893" or p2c.categories_id = "91894" or p2c.categories_id = "91895" or p2c.categories_id = "91896" or p2c.categories_id = "91897" or p2c.categories_id = "91898" or p2c.categories_id = "91899" or p2c.categories_id = "91900" or p2c.categories_id = "91901" or p2c.categories_id = "91902" or p2c.categories_id = "91903" or p2c.categories_id = "91904" or p2c.categories_id = "91905" or p2c.categories_id = "91906" or p2c.categories_id = "91907" or p2c.categories_id = "91908" or p2c.categories_id = "91909" or p2c.categories_id = "93986" or p2c.categories_id = "93987" or p2c.categories_id = "93988" or p2c.categories_id = "93989" or p2c.categories_id = "93990" or p2c.categories_id = "93991" or p2c.categories_id = "93992" or p2c.categories_id = "91910" or p2c.categories_id = "91911" or p2c.categories_id = "94028" or p2c.categories_id = "94029" or p2c.categories_id = "94030" or p2c.categories_id = "94031" or p2c.categories_id = "94032" or p2c.categories_id = "94033" or p2c.categories_id = "94034" or p2c.categories_id = "94035" or p2c.categories_id = "92114" or p2c.categories_id = "92115" or p2c.categories_id = "92116" or p2c.categories_id = "92117" or p2c.categories_id = "92118" or p2c.categories_id = "92119" or p2c.categories_id = "92120" or p2c.categories_id = "92123" or p2c.categories_id = "92124" or p2c.categories_id = "92126" or p2c.categories_id = "92130" or p2c.categories_id = "92132" or p2c.categories_id = "92135" or p2c.categories_id = "92136" or p2c.categories_id = "92137" or p2c.categories_id = "92138" or p2c.categories_id = "92139" or p2c.categories_id = "92140" or p2c.categories_id = "92141" or p2c.categories_id = "92142" or p2c.categories_id = "92143" or p2c.categories_id = "92144" or p2c.categories_id = "92147" or p2c.categories_id = "92148" or p2c.categories_id = "92149" or p2c.categories_id = "92150" or p2c.categories_id = "92151" or p2c.categories_id = "92152" or p2c.categories_id = "92153" or p2c.categories_id = "92154" or p2c.categories_id = "92157" or p2c.categories_id = "92158" or p2c.categories_id = "92159" or p2c.categories_id = "92160" or p2c.categories_id = "92161" or p2c.categories_id = "92162" or p2c.categories_id = "92163" or p2c.categories_id = "92164" or p2c.categories_id = "92165" or p2c.categories_id = "92170" or p2c.categories_id = "92171" or p2c.categories_id = "93768" or p2c.categories_id = "93773" or p2c.categories_id = "93772" or p2c.categories_id = "93771" or p2c.categories_id = "93769" or p2c.categories_id = "93770" or p2c.categories_id = "93775" or p2c.categories_id = "93776" or p2c.categories_id = "93774" or p2c.categories_id = "92176" or p2c.categories_id = "92177" or p2c.categories_id = "92178" or p2c.categories_id = "92179" or p2c.categories_id = "92181" or p2c.categories_id = "92182" or p2c.categories_id = "92183" or p2c.categories_id = "92184" or p2c.categories_id = "92185" or p2c.categories_id = "92186" or p2c.categories_id = "92187" or p2c.categories_id = "92188" or p2c.categories_id = "92189" or p2c.categories_id = "92190" or p2c.categories_id = "92191" or p2c.categories_id = "92192" or p2c.categories_id = "92193" or p2c.categories_id = "92194" or p2c.categories_id = "92195" or p2c.categories_id = "92196" or p2c.categories_id = "92198" or p2c.categories_id = "92199" or p2c.categories_id = "92200" or p2c.categories_id = "92201" or p2c.categories_id = "92202" or p2c.categories_id = "92203" or p2c.categories_id = "92204" or p2c.categories_id = "92205" or p2c.categories_id = "92206" or p2c.categories_id = "92207" or p2c.categories_id = "92208" or p2c.categories_id = "92209" or p2c.categories_id = "92211" or p2c.categories_id = "92212" or p2c.categories_id = "92213" or p2c.categories_id = "92215" or p2c.categories_id = "92217" or p2c.categories_id = "92219" or p2c.categories_id = "92222" or p2c.categories_id = "92224" or p2c.categories_id = "92225" or p2c.categories_id = "92226" or p2c.categories_id = "92227" or p2c.categories_id = "92228" or p2c.categories_id = "92229" or p2c.categories_id = "92231" or p2c.categories_id = "92232" or p2c.categories_id = "92326" or p2c.categories_id = "92328" or p2c.categories_id = "92330" or p2c.categories_id = "92331" or p2c.categories_id = "92332" or p2c.categories_id = "92333" or p2c.categories_id = "92334" or p2c.categories_id = "92335" or p2c.categories_id = "92336" or p2c.categories_id = "92337" or p2c.categories_id = "92338" or p2c.categories_id = "92339" or p2c.categories_id = "92340" or p2c.categories_id = "92341" or p2c.categories_id = "92342" or p2c.categories_id = "92343" or p2c.categories_id = "92344" or p2c.categories_id = "92345" or p2c.categories_id = "92346" or p2c.categories_id = "92347" or p2c.categories_id = "92348" or p2c.categories_id = "92349" or p2c.categories_id = "92351" or p2c.categories_id = "92353" or p2c.categories_id = "92355" or p2c.categories_id = "92357" or p2c.categories_id = "92358" or p2c.categories_id = "92359" or p2c.categories_id = "92360" or p2c.categories_id = "92361" or p2c.categories_id = "92362" or p2c.categories_id = "92363" or p2c.categories_id = "92323" or p2c.categories_id = "92366" or p2c.categories_id = "92367" or p2c.categories_id = "92368" or p2c.categories_id = "92370" or p2c.categories_id = "92374" or p2c.categories_id = "92376" or p2c.categories_id = "92377" or p2c.categories_id = "92379" or p2c.categories_id = "92380" or p2c.categories_id = "92381" or p2c.categories_id = "92382" or p2c.categories_id = "92385" or p2c.categories_id = "92391" or p2c.categories_id = "92392" or p2c.categories_id = "92393" or p2c.categories_id = "93874" or p2c.categories_id = "93876" or p2c.categories_id = "93875" or p2c.categories_id = "93877" or p2c.categories_id = "93879" or p2c.categories_id = "93878" or p2c.categories_id = "93861" or p2c.categories_id = "93862" or p2c.categories_id = "93865" or p2c.categories_id = "93864" or p2c.categories_id = "93863" or p2c.categories_id = "93866" or p2c.categories_id = "93850" or p2c.categories_id = "93851" or p2c.categories_id = "93853" or p2c.categories_id = "93852" or p2c.categories_id = "93826" or p2c.categories_id = "93828" or p2c.categories_id = "93832" or p2c.categories_id = "93830" or p2c.categories_id = "93831" or p2c.categories_id = "93829" or p2c.categories_id = "93827" or p2c.categories_id = "93835" or p2c.categories_id = "93834" or p2c.categories_id = "93833" or p2c.categories_id = "93788" or p2c.categories_id = "93790" or p2c.categories_id = "93789" or p2c.categories_id = "93779" or p2c.categories_id = "93781" or p2c.categories_id = "93780" or p2c.categories_id = "93783" or p2c.categories_id = "93782" or p2c.categories_id = "93791" or p2c.categories_id = "93792" or p2c.categories_id = "93793" or p2c.categories_id = "93777" or p2c.categories_id = "93778" or p2c.categories_id = "92552" or p2c.categories_id = "92553" or p2c.categories_id = "92558" or p2c.categories_id = "92559" or p2c.categories_id = "92560" or p2c.categories_id = "92561" or p2c.categories_id = "92568" or p2c.categories_id = "92569" or p2c.categories_id = "92570" or p2c.categories_id = "92571" or p2c.categories_id = "92572" or p2c.categories_id = "92573" or p2c.categories_id = "92574" or p2c.categories_id = "92575" or p2c.categories_id = "92576" or p2c.categories_id = "92578" or p2c.categories_id = "92579" or p2c.categories_id = "92580" or p2c.categories_id = "92581" or p2c.categories_id = "92582" or p2c.categories_id = "92583" or p2c.categories_id = "92584" or p2c.categories_id = "92585" or p2c.categories_id = "92587" or p2c.categories_id = "92588" or p2c.categories_id = "92589" or p2c.categories_id = "92590" or p2c.categories_id = "92591" or p2c.categories_id = "92592" or p2c.categories_id = "92596" or p2c.categories_id = "92597" or p2c.categories_id = "92599" or p2c.categories_id = "92600" or p2c.categories_id = "92602" or p2c.categories_id = "92603" or p2c.categories_id = "92604" or p2c.categories_id = "92605" or p2c.categories_id = "92607" or p2c.categories_id = "92608" or p2c.categories_id = "92609" or p2c.categories_id = "92610" or p2c.categories_id = "92611" or p2c.categories_id = "92612" or p2c.categories_id = "92613" or p2c.categories_id = "92614" or p2c.categories_id = "92615" or p2c.categories_id = "92616" or p2c.categories_id = "92617" or p2c.categories_id = "92618" or p2c.categories_id = "92619" or p2c.categories_id = "92638" or p2c.categories_id = "92639" or p2c.categories_id = "92640" or p2c.categories_id = "92641" or p2c.categories_id = "92642" or p2c.categories_id = "92643" or p2c.categories_id = "92645" or p2c.categories_id = "92646" or p2c.categories_id = "92647" or p2c.categories_id = "92648" or p2c.categories_id = "92649" or p2c.categories_id = "92650" or p2c.categories_id = "92651" or p2c.categories_id = "92652" or p2c.categories_id = "92653" or p2c.categories_id = "92654" or p2c.categories_id = "92655" or p2c.categories_id = "92656" or p2c.categories_id = "92657" or p2c.categories_id = "92658" or p2c.categories_id = "92659" or p2c.categories_id = "92660" or p2c.categories_id = "92661" or p2c.categories_id = "92662" or p2c.categories_id = "92663" or p2c.categories_id = "92664" or p2c.categories_id = "92665" or p2c.categories_id = "92666" or p2c.categories_id = "92667" or p2c.categories_id = "92668" or p2c.categories_id = "92669" or p2c.categories_id = "92670" or p2c.categories_id = "92671" or p2c.categories_id = "92672" or p2c.categories_id = "92681" or p2c.categories_id = "92682" or p2c.categories_id = "92683" or p2c.categories_id = "92684" or p2c.categories_id = "92685" or p2c.categories_id = "92686" or p2c.categories_id = "92817" or p2c.categories_id = "92818" or p2c.categories_id = "92819" or p2c.categories_id = "92821" or p2c.categories_id = "92860" or p2c.categories_id = "92861" or p2c.categories_id = "92863" or p2c.categories_id = "92864" or p2c.categories_id = "92941" or p2c.categories_id = "92942" or p2c.categories_id = "92948" or p2c.categories_id = "92949" or p2c.categories_id = "92950" or p2c.categories_id = "92951" or p2c.categories_id = "92952" or p2c.categories_id = "92953" or p2c.categories_id = "92959" or p2c.categories_id = "92961" or p2c.categories_id = "92962" or p2c.categories_id = "92964" or p2c.categories_id = "92965" or p2c.categories_id = "92966" or p2c.categories_id = "92967" or p2c.categories_id = "92968" or p2c.categories_id = "92969" or p2c.categories_id = "92970" or p2c.categories_id = "92971" or p2c.categories_id = "92972" or p2c.categories_id = "92973" or p2c.categories_id = "92975" or p2c.categories_id = "92976" or p2c.categories_id = "92977" or p2c.categories_id = "92978" or p2c.categories_id = "92979" or p2c.categories_id = "92980" or p2c.categories_id = "92981" or p2c.categories_id = "92982" or p2c.categories_id = "92983" or p2c.categories_id = "92984" or p2c.categories_id = "92985" or p2c.categories_id = "92986" or p2c.categories_id = "92989" or p2c.categories_id = "92991" or p2c.categories_id = "92992" or p2c.categories_id = "92994" or p2c.categories_id = "92995" or p2c.categories_id = "92996" or p2c.categories_id = "92997" or p2c.categories_id = "92998" or p2c.categories_id = "92999" or p2c.categories_id = "93000" or p2c.categories_id = "93002" or p2c.categories_id = "93054" or p2c.categories_id = "93055" or p2c.categories_id = "93056" or p2c.categories_id = "93057" or p2c.categories_id = "93069" or p2c.categories_id = "93070" or p2c.categories_id = "93071" or p2c.categories_id = "93101" or p2c.categories_id = "93102" or p2c.categories_id = "93103" or p2c.categories_id = "93105" or p2c.categories_id = "93106" or p2c.categories_id = "93108" or p2c.categories_id = "93109" or p2c.categories_id = "93110" or p2c.categories_id = "93111" or p2c.categories_id = "93112" or p2c.categories_id = "93113" or p2c.categories_id = "93114" or p2c.categories_id = "93115" or p2c.categories_id = "93116" or p2c.categories_id = "93117" or p2c.categories_id = "93118" or p2c.categories_id = "93119" or p2c.categories_id = "93120" or p2c.categories_id = "93121" or p2c.categories_id = "93122" or p2c.categories_id = "93123" or p2c.categories_id = "93125" or p2c.categories_id = "93133" or p2c.categories_id = "93134" or p2c.categories_id = "93135" or p2c.categories_id = "93136" or p2c.categories_id = "93137" or p2c.categories_id = "93138" or p2c.categories_id = "93139" or p2c.categories_id = "93140" or p2c.categories_id = "93141" or p2c.categories_id = "93142" or p2c.categories_id = "93143" or p2c.categories_id = "93144" or p2c.categories_id = "91719" or p2c.categories_id = "91720" or p2c.categories_id = "91721" or p2c.categories_id = "93145" or p2c.categories_id = "93146" or p2c.categories_id = "93148" or p2c.categories_id = "93156" or p2c.categories_id = "93157" or p2c.categories_id = "93158" or p2c.categories_id = "93161" or p2c.categories_id = "93162" or p2c.categories_id = "93164" or p2c.categories_id = "93165" or p2c.categories_id = "93166" or p2c.categories_id = "93167" or p2c.categories_id = "93168" or p2c.categories_id = "93169" or p2c.categories_id = "93170" or p2c.categories_id = "93171" or p2c.categories_id = "93172" or p2c.categories_id = "93173" or p2c.categories_id = "93174" or p2c.categories_id = "93175" or p2c.categories_id = "93176" or p2c.categories_id = "93177" or p2c.categories_id = "93178" or p2c.categories_id = "93179" or p2c.categories_id = "93181" or p2c.categories_id = "93183" or p2c.categories_id = "93184" or p2c.categories_id = "93185" or p2c.categories_id = "93186" or p2c.categories_id = "93187" or p2c.categories_id = "93188" or p2c.categories_id = "93191" or p2c.categories_id = "93194" or p2c.categories_id = "93195" or p2c.categories_id = "93196" or p2c.categories_id = "93199" or p2c.categories_id = "93208" or p2c.categories_id = "93809" or p2c.categories_id = "93811" or p2c.categories_id = "93810" or p2c.categories_id = "93805" or p2c.categories_id = "93807" or p2c.categories_id = "93808" or p2c.categories_id = "93806" or p2c.categories_id = "93209" or p2c.categories_id = "93210" or p2c.categories_id = "93212" or p2c.categories_id = "93213" or p2c.categories_id = "93214" or p2c.categories_id = "93215" or p2c.categories_id = "93216" or p2c.categories_id = "93217" or p2c.categories_id = "93218" or p2c.categories_id = "93219" or p2c.categories_id = "93220" or p2c.categories_id = "93221" or p2c.categories_id = "93222" or p2c.categories_id = "93223" or p2c.categories_id = "93224" or p2c.categories_id = "93225" or p2c.categories_id = "93226" or p2c.categories_id = "93227" or p2c.categories_id = "93228" or p2c.categories_id = "93229" or p2c.categories_id = "93230" or p2c.categories_id = "93794" or p2c.categories_id = "93796" or p2c.categories_id = "93795" or p2c.categories_id = "93238" or p2c.categories_id = "93239" or p2c.categories_id = "93240" or p2c.categories_id = "93241" or p2c.categories_id = "93242" or p2c.categories_id = "93244" or p2c.categories_id = "93245" or p2c.categories_id = "93246" or p2c.categories_id = "93247" or p2c.categories_id = "93909" or p2c.categories_id = "93910" or p2c.categories_id = "93911" or p2c.categories_id = "93912" or p2c.categories_id = "93913" or p2c.categories_id = "93914" or p2c.categories_id = "93915" or p2c.categories_id = "93916" or p2c.categories_id = "93917" or p2c.categories_id = "93918" or p2c.categories_id = "93919" or p2c.categories_id = "93920" or p2c.categories_id = "93921" or p2c.categories_id = "93922" or p2c.categories_id = "93923" or p2c.categories_id = "93924" or p2c.categories_id = "93925" or p2c.categories_id = "93926" or p2c.categories_id = "93927" or p2c.categories_id = "93928" or p2c.categories_id = "93929" or p2c.categories_id = "93930" or p2c.categories_id = "93931" or p2c.categories_id = "93932" or p2c.categories_id = "93933" or p2c.categories_id = "93934" or p2c.categories_id = "93935" or p2c.categories_id = "93936" or p2c.categories_id = "93937" or p2c.categories_id = "93938" or p2c.categories_id = "93939" or p2c.categories_id = "93940" or p2c.categories_id = "93941" or p2c.categories_id = "93942" or p2c.categories_id = "93943" or p2c.categories_id = "93944" or p2c.categories_id = "93945" or p2c.categories_id = "93946" or p2c.categories_id = "93947" or p2c.categories_id = "93948" or p2c.categories_id = "93949" or p2c.categories_id = "93950" or p2c.categories_id = "93951" or p2c.categories_id = "93952" or p2c.categories_id = "93953" or p2c.categories_id = "93954" or p2c.categories_id = "93955" or p2c.categories_id = "93956" or p2c.categories_id = "93957" or p2c.categories_id = "93958" or p2c.categories_id = "93959" or p2c.categories_id = "93960" or p2c.categories_id = "93961" or p2c.categories_id = "93962" or p2c.categories_id = "93963" or p2c.categories_id = "93964" or p2c.categories_id = "93965" or p2c.categories_id = "93966" or p2c.categories_id = "93967" or p2c.categories_id = "93968" or p2c.categories_id = "93969" or p2c.categories_id = "93970" or p2c.categories_id = "93971" or p2c.categories_id = "93972" or p2c.categories_id = "93973" or p2c.categories_id = "93974" or p2c.categories_id = "93975" or p2c.categories_id = "93976" or p2c.categories_id = "93977" or p2c.categories_id = "93978" or p2c.categories_id = "93979" or p2c.categories_id = "93996" or p2c.categories_id = "93997" or p2c.categories_id = "94007" or p2c.categories_id = "94008" or p2c.categories_id = "94009" or p2c.categories_id = "94010" or p2c.categories_id = "94011" or p2c.categories_id = "94012" or p2c.categories_id = "94013" or p2c.categories_id = "94014" or p2c.categories_id = "94015" or p2c.categories_id = "94016" or p2c.categories_id = "94017" or p2c.categories_id = "94018" or p2c.categories_id = "94019" or p2c.categories_id = "94020" or p2c.categories_id = "94021" or p2c.categories_id = "94022" or p2c.categories_id = "94023" or p2c.categories_id = "94024" or p2c.categories_id = "94025" or p2c.categories_id = "94026" or p2c.categories_id = "94027") AND pd.language_id = "4" GROUP BY epf.epf_value ORDER BY epf.sort_order, epf.epf_value
C'est quoi qui peut pondre une requête pareille ? OMG. En plus pour sortir ça, il a dû falloir déjà faire pas mal de requêtes pour sortir ces 1259 ids de catégorie...
En bas de tes pages il y a ça :
Current Parse Time: 1.116 s with 7927 queries
Current Parse Time: 1.101 s with 7691 queries
Current Parse Time: 1.076 s with 7463 queries
7463 requêtes !!!! Pour afficher une page de produit !!!!
Je crois que je commence à comprendre d'où viennent tous ces ids de catégorie...
Bon en plus pour une fois sur le SdZ ton site n'est pas un mmorpg à la con ou un site de ddl, mais un vrai site sérieux (inventaire impressionnant d'ailleurs). Donc je suis peiné...
Honnêtement, si c'est ton "prestataire" qui a fait ça, donne des noms.
> Tu dois avoir un problème de mesure...
> Utilise le profiler de mysql pour avoir des infos fiables.
C'est la liste interminable de catégories qui met la zone, si on la vire la requête prend 0.6s (toujours excessivement lent mais quand même 60x moins !)... avec un IN() c'est pareil (pardon je me croyais sous postgres, lol).
Alors oui c'est un prestataire (codeur.com) qui est à l'origine de cette requête et l'ajout de données dans notre site grossit de jour en jour cette requête (au début c'était a peu prés correct), d'où ma volonté de trouver une solution pour pallier au problème avant l'ajout de données.
Voici ce que j'ai essayé :
Dans PRODUCTS_TO_CATEGORIES :
j'ai ajouté 3 champs
n1,n2,n3
Je stock donc la valeur de la catégorie mère pour m'éviter les horribles or p2c.categories = démultiplié, du coup la même requête que tu as cité la haut pour les billets devient :
SELECT DISTINCT epf.epf_value, epf.sort_order, epf.epf_id , epf.value_id FROM products p LEFT JOIN products_description pd on (p.products_id = pd.products_id) LEFT JOIN products_to_categories p2c on (p.products_id = p2c.products_id) LEFT JOIN extra_field_values AS epf ON (epf.value_id = extra_value_id2 or epf.value_id = extra_value_id3 or epf.value_id = extra_value_id4 or epf.value_id = extra_value_id5 or epf.value_id = extra_value_id6 or epf.value_id = extra_value_id8 or epf.value_id = extra_value_id9 ) and (epf.languages_id = "4") WHERE p2c.n2 = "91041" AND p.products_status = "1" AND p.vendu = "0" AND pd.language_id = "4" GROUP BY epf.epf_value ORDER BY epf.sort_order, epf.epf_value
La requête est plus lisible mais le temps de chargement reste exactement le même.
Je suis prêt à travailler la dessus quitte à revoir des champs dans ma BDD et c'est bien pour ça que je poste ici pour des conseils sur la marche à suivre.
Aurais tu une piste pour optimiser tout ça sans refaire une nouvelle base ?
C'est le problème avec les codeurs boulets qui n'y connaissent rien en BDD : ça marche toujours très bien sur une base minuscule avec 50 produits, mais sur une base (toujours petite) avec 10000 produits c'est l'effondrement total.
Toutes les tables sont en MyISAM, aucune foreign key, parent_id=0 au lieu de NULL dans les catégories pour noter la racine (donc impossible de mettre une contrainte en l'état), etc. Enfin c'est du osCommerce quoi. Par défaut t'as pas de transactions, aucune intégrité garantie. On trouvait parfois des commandes avec n'importe quoi dedans, ou des trucs bizarres... rendre ce truc utilisable est un gros boulot.
Tu vas devoir modifier pas mal le code.
Oublie ton histoire de 3 champs dans PRODUCTS_TO_CATEGORIES, c'est atroce.
Pour manipuler des arbres, MySQL est nul : pas de requêtes WITH RECURSIVE !... Mais on va lui apprendre.
Ça c'est une gestion d'arbre toute simple. La colonne parent_id reste mais on dénormalise en ajoutant une colonne "path" qui contient le chemin d'accès (comme un chemin defichier) avec la liste des parents. Par exemple le chemin de la catégorie 89915 est "89509.89915" (son parent est 89509, et il n'a pas de parent) et pour obtenir tous ses enfants, on fait UNE SEULE requête qui utilise l'index, en mettant comme chemin : LIKE '89509.89915.%';
Il faudra tenir ça à jour quand tu modifies les catégories.
Si tu modifies ton code pour utiliser cette méthode, tu passeras de 7000 requêtes par page à quelque chose de plus raisonnable...
Quand à tes extra_machins, la seule solution est de virer les colonnes numérotées et de mettre ça sous forme de table de lien (relation N-N).
> ajoute un index sur extra_field_values (languages_id, value_id)
Le problème c'est que MySQL n'arrive pas à optimiser le JOIN ON epf.value_id IN ( extra_value_id2, extra_value_id3, ... ) à cause de la conception minable de cette table !
Oui je fais des mises à jour via import en cron donc c'est pas un souci de ce côté là
Voici ce que ça donne au final en dur :
SELECT DISTINCT epf.epf_value, epf.sort_order, epf.epf_id , epf.value_id
FROM products p
INNER JOIN products_description pd on p.products_id = pd.products_id
INNER JOIN products_to_categories p2c
ON pd.language_id = 4
AND pd.products_id = p2c.products_id
AND p.vendu = "0"
and p.products_status = "1"
INNER JOIN categories c
ON c.categories_id = p2c.categories_id
AND c.path LIKE '89549.91041.%'
LEFT JOIN product_to_extra_field_value p2efv
ON pd.products_id = p2efv.products_id
AND pd.language_id = 4
LEFT JOIN extra_field_values epf
ON epf.languages_id = 4
AND epf.value_id = p2efv.value_id
ORDER BY epf.sort_order, epf.epf_value
Je vais de ce pas modifier mon oscommerce pour lui donner la nouvelle syntaxe ;-)
1000 merci à vous deux pour le temps passé.
Je ne ferme pas ce topic, je le ferais après avoir modifié mon code.
On peut changer ça un petit peu (en incluant le chemin de la table catégories histoire de ne pas avoir à générer cet énorme IN)
ALTER TABLE products_to_categories DROP KEY products_id, DROP KEY categories_id, DROP PRIMARY KEY, ADD PRIMARY KEY (categories_id, products_id), ADD KEY ( products_id, categories_id );
show create table products_to_categories;
| Table | Create Table | products_to_categories | CREATE TABLE `products_to_categories` (
`products_id` int(11) NOT NULL,
`categories_id` int(11) NOT NULL,
PRIMARY KEY (`categories_id`,`products_id`),
KEY `categories_id` (`categories_id`,`products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
ALTER TABLE products_description DROP PRIMARY KEY, DROP KEY language_id, ADD PRIMARY KEY (language_id, products_id), ADD KEY( products_id, language_id );
ALTER TABLE product_to_extra_field_value ADD KEY ( language_id, products_id, value_id );
-- ta requête devient:
SELECT epf.epf_value, epf.sort_order, epf.epf_id , epf.value_id
FROM (SELECT DISTINCT p2efv.value_id
FROM categories c
JOIN products_to_categories p2c ON (c.categories_id = p2c.categories_id)
JOIN products_description pd ON (pd.language_id = 4 AND pd.products_id = p2c.products_id)
JOIN product_to_extra_field_value p2efv ON (pd.products_id = p2efv.products_id AND pd.language_id = 4)
WHERE c.path like '89549.%'
) x
JOIN extra_field_values epf ON (epf.languages_id = 4 AND epf.value_id = x.value_id)
ORDER BY epf.sort_order, epf.epf_value
hop, 0.1s
tu vas devoir refaire une passe sur toutes les box à la con de oscommerce qui listent des produits et des catégories : en général par défaut elles font des requêtes dans des boucles, c'est pour ça que tu as 7000 requêtes par page...
EDIT : lol.
Pour déconner j'ai mis ça dans Postgres...
Requête monstrueuse ci-dessus avec IN(une montagne de valeurs) : 2.6s
Celle de ce message : 0.1s
(sans index puisque les tables sont parcourues presque en entier...)
J'ai commencé à intégrer à ma boutique oscommerce pour l'instant seulement sur la partie filtres, je dois encore effectuer des tests pour vérifier l'influence sur la structure d'oscommerce de telles modifications (diverses boxes), donc je préfère être prudent et prendre le temps de tester sur ma version miroir.
En fait les 7000 requêtes qui sont affichées dans mon footer sont liées à une fonction Oscommerce qui compte les produits par catégories (7000 requêtes identiques ... select count), je vais la remplacer par une lecture dans la BDD pour limiter le massacre.
Grâce à votre aide le serveur sql respire un peu mieux ;-)
1000 Merci encore,
Cdt
Regexp sur jointure mysql
× Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
× Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.
Tutoriel complet MySQL !
Tutoriel complet MySQL !
Tutoriel complet MySQL !