need help with my search

[Guest]

Hey guyz,

 

I have a webshop with over 1000 products and when i search normaly i have no problems with the speed.

But when i search in a categorie it takes up to 15+ sec to finish the search!!

 

Does anyone have a solution for this? i try'd to fix the query but it still is slow......is there a new searchbox out there thats super fast? (advanced_search.php)

Or a new query that will speed it up?

 

Thanx.

 

Gigre

[♥bruyndoncx]

probably you should be adding some indexes to your database.

if you are able to log the query and try it interactively in mysql using the EXPLAIN command you can see how the query is interpreted and what makes it slow.

To get started, perhaps you can check out this contribution to log the problematic queries

http://www.oscommerce.com/community/contri...rch,performance

 

Have you added indexes to your database ?

Have you customized searches or is it all standard osC MS2 stuff ?

[Guest]

its all standard, but i might have added a extra search in categorie box to it.....it was a long time ago i made the shop, now testing it fully i came across this problem :)

 

 

And the DB has the index on the name, so thats good, the strange part is that it works fast and great when i search the normal way, but when i search in a categorie it gets really slow!

[Guest]

its about this code:

 

if (isset($HTTP_GET_VARS['categories_id']) && tep_not_null($HTTP_GET_VARS['categories_id'])) {

if (isset($HTTP_GET_VARS['inc_subcat']) && ($HTTP_GET_VARS['inc_subcat'] == '1')) {

$subcategories_array = array();

tep_get_subcategories($subcategories_array, $HTTP_GET_VARS['categories_id']);

 

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

 

for ($i=0, $n=sizeof($subcategories_array); $i<$n; $i++ ) {

$where_str .= " or p2c.categories_id = '" . (int)$subcategories_array[$i] . "'";

}

 

$where_str .= ")";

} else {

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

}

}

 

 

 

 

 

When i // the $where_str it does it the normal way (good speed)

 

But then it does't search on the categorie anymore.. :(

 

I only have 5 categorie's to search in, i have ALOT of subcategorie's and i want to search in the subcategorie's to, but i never directly select the subcategorie, only the top under the root!

 

Help... ? :)

[boxtel]

its about this code:

 

if (isset($HTTP_GET_VARS['categories_id']) && tep_not_null($HTTP_GET_VARS['categories_id'])) {

if (isset($HTTP_GET_VARS['inc_subcat']) && ($HTTP_GET_VARS['inc_subcat'] == '1')) {

$subcategories_array = array();

tep_get_subcategories($subcategories_array, $HTTP_GET_VARS['categories_id']);

 

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

 

for ($i=0, $n=sizeof($subcategories_array); $i<$n; $i++ ) {

$where_str .= " or p2c.categories_id = '" . (int)$subcategories_array[$i] . "'";

}

 

$where_str .= ")";

} else {

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

}

}

When i // the $where_str it does it the normal way (good speed)

 

But then it does't search on the categorie anymore.. :(

 

I only have 5 categorie's to search in, i have ALOT of subcategorie's and i want to search in the subcategorie's to, but i never directly select the subcategorie, only the top under the root!

 

Help... ? :)

 

in advanced_search_result.php, after this code:

 

$listing_sql = $select_str . $from_str . $where_str . $order_str;

 

you add (for testing only):

 

echo $listing_sql;

 

then you can see what query is constructed and see which indexes are required.

[Guest]

With categorie=1 it is:

 

select distinct m.manufacturers_id, p.products_id, pd.products_name, pd.products_description, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '1' or p2c.categories_id = '2' or p2c.categories_id = '3' or p2c.categories_id = '4' or p2c.categories_id = '5' or p2c.categories_id = '6' or p2c.categories_id = '7' or p2c.categories_id = '8' or p2c.categories_id = '9' or p2c.categories_id = '10' or p2c.categories_id = '11' or p2c.categories_id = '12' or p2c.categories_id = '13' or p2c.categories_id = '14' or p2c.categories_id = '15' or p2c.categories_id = '16' or p2c.categories_id = '17' or p2c.categories_id = '18' or p2c.categories_id = '22' or p2c.categories_id = '32' or p2c.categories_id = '33' or p2c.categories_id = '36' or p2c.categories_id = '37' or p2c.categories_id = '38' or p2c.categories_id = '39' or p2c.categories_id = '40' or p2c.categories_id = '41' or p2c.categories_id = '42' or p2c.categories_id = '43' or p2c.categories_id = '44' or p2c.categories_id = '45' or p2c.categories_id = '46' or p2c.categories_id = '47' or p2c.categories_id = '48' or p2c.categories_id = '51' or p2c.categories_id = '53' or p2c.categories_id = '54' or p2c.categories_id = '55' or p2c.categories_id = '56' or p2c.categories_id = '57' or p2c.categories_id = '58' or p2c.categories_id = '59' or p2c.categories_id = '60' or p2c.categories_id = '61' or p2c.categories_id = '62' or p2c.categories_id = '63' or p2c.categories_id = '64' or p2c.categories_id = '65' or p2c.categories_id = '66' or p2c.categories_id = '67' or p2c.categories_id = '68' or p2c.categories_id = '69' or p2c.categories_id = '70' or p2c.categories_id = '71' or p2c.categories_id = '72' or p2c.categories_id = '73' or p2c.categories_id = '74' or p2c.categories_id = '75' or p2c.categories_id = '76' or p2c.categories_id = '77' or p2c.categories_id = '78' or p2c.categories_id = '79' or p2c.categories_id = '80' or p2c.categories_id = '81' or p2c.categories_id = '82' or p2c.categories_id = '83' or p2c.categories_id = '84' or p2c.categories_id = '85' or p2c.categories_id = '86' or p2c.categories_id = '87' or p2c.categories_id = '88' or p2c.categories_id = '89' or p2c.categories_id = '90' or p2c.categories_id = '91' or p2c.categories_id = '92' or p2c.categories_id = '93' or p2c.categories_id = '94' or p2c.categories_id = '95' or p2c.categories_id = '96' or p2c.categories_id = '97' or p2c.categories_id = '98' or p2c.categories_id = '99' or p2c.categories_id = '100' or p2c.categories_id = '101' or p2c.categories_id = '102' or p2c.categories_id = '103' or p2c.categories_id = '104' or p2c.categories_id = '782' or p2c.categories_id = '783' or p2c.categories_id = '784' or p2c.categories_id = '785' or p2c.categories_id = '786' or p2c.categories_id = '787' or p2c.categories_id = '788' or p2c.categories_id = '789' or p2c.categories_id = '790' or p2c.categories_id = '791' or p2c.categories_id = '792' or p2c.categories_id = '793' or p2c.categories_id = '794' or p2c.categories_id = '795' or p2c.categories_id = '796' or p2c.categories_id = '797' or p2c.categories_id = '798' or p2c.categories_id = '799' or p2c.categories_id = '800' or p2c.categories_id = '801' or p2c.categories_id = '802' or p2c.categories_id = '803' or p2c.categories_id = '804' or p2c.categories_id = '805' or p2c.categories_id = '806' or p2c.categories_id = '807' or p2c.categories_id = '808' or p2c.categories_id = '809' or p2c.categories_id = '810' or p2c.categories_id = '811' or p2c.categories_id = '812' or p2c.categories_id = '813' or p2c.categories_id = '814' or p2c.categories_id = '815' or p2c.categories_id = '816' or p2c.categories_id = '817' or p2c.categories_id = '818' or p2c.categories_id = '819' or p2c.categories_id = '2030' or p2c.categories_id = '2031' or p2c.categories_id = '2032' or p2c.categories_id = '2033' or p2c.categories_id = '2034' or p2c.categories_id = '2035' or p2c.categories_id = '2036' or p2c.categories_id = '2037' or p2c.categories_id = '2038' or p2c.categories_id = '2039' or p2c.categories_id = '2048' or p2c.categories_id = '2049' or p2c.categories_id = '2050' or p2c.categories_id = '2051' or p2c.categories_id = '2052' or p2c.categories_id = '2053' or p2c.categories_id = '2054' or p2c.categories_id = '2143' or p2c.categories_id = '2144' or p2c.categories_id = '2210' or p2c.categories_id = '2211' or p2c.categories_id = '2224' or p2c.categories_id = '2225' or p2c.categories_id = '2238' or p2c.categories_id = '2239' or p2c.categories_id = '2240' or p2c.categories_id = '2352' or p2c.categories_id = '2440' or p2c.categories_id = '2441' or p2c.categories_id = '2459' or p2c.categories_id = '2460' or p2c.categories_id = '2461' or p2c.categories_id = '2462' or p2c.categories_id = '2463' or p2c.categories_id = '2464' or p2c.categories_id = '2465' or p2c.categories_id = '2466' or p2c.categories_id = '2467' or p2c.categories_id = '2468' or p2c.categories_id = '105' or p2c.categories_id = '106' or p2c.categories_id = '107' or p2c.categories_id = '108' or p2c.categories_id = '109' or p2c.categories_id = '110' or p2c.categories_id = '111' or p2c.categories_id = '112' or p2c.categories_id = '113' or p2c.categories_id = '114' or p2c.categories_id = '115' or p2c.categories_id = '116' or p2c.categories_id = '117' or p2c.categories_id = '118' or p2c.categories_id = '119' or p2c.categories_id = '120' or p2c.categories_id = '121' or p2c.categories_id = '122' or p2c.categories_id = '123' or p2c.categories_id = '124' or p2c.categories_id = '125' or p2c.categories_id = '126' or p2c.categories_id = '127' or p2c.categories_id = '128' or p2c.categories_id = '129' or p2c.categories_id = '130' or p2c.categories_id = '131' or p2c.categories_id = '132' or p2c.categories_id = '133' or p2c.categories_id = '134' or p2c.categories_id = '135' or p2c.categories_id = '136' or p2c.categories_id = '137' or p2c.categories_id = '138' or p2c.categories_id = '139' or p2c.categories_id = '140' or p2c.categories_id = '141' or p2c.categories_id = '142' or p2c.categories_id = '143' or p2c.categories_id = '144' or p2c.categories_id = '145' or p2c.categories_id = '146' or p2c.categories_id = '147' or p2c.categories_id = '148' or p2c.categories_id = '149' or p2c.categories_id = '150' or p2c.categories_id = '151' or p2c.categories_id = '152' or p2c.categories_id = '153' or p2c.categories_id = '154' or p2c.categories_id = '155' or p2c.categories_id = '156' or p2c.categories_id = '157' or p2c.categories_id = '158' or p2c.categories_id = '159' or p2c.categories_id = '160' or p2c.categories_id = '161' or p2c.categories_id = '162' or p2c.categories_id = '163' or p2c.categories_id = '164' or p2c.categories_id = '165' or p2c.categories_id = '166' or p2c.categories_id = '167' or p2c.categories_id = '168' or p2c.categories_id = '169' or p2c.categories_id = '170' or p2c.categories_id = '171' or p2c.categories_id = '172' or p2c.categories_id = '213' or p2c.categories_id = '214' or p2c.categories_id = '215' or p2c.categories_id = '216' or p2c.categories_id = '217' or p2c.categories_id = '218' or p2c.categories_id = '219' or p2c.categories_id = '220' or p2c.categories_id = '221' or p2c.categories_id = '222' or p2c.categories_id = '223' or p2c.categories_id = '224' or p2c.categories_id = '225' or p2c.categories_id = '226' or p2c.categories_id = '227' or p2c.categories_id = '228' or p2c.categories_id = '229' or p2c.categories_id = '230' or p2c.categories_id = '231' or p2c.categories_id = '232' or p2c.categories_id = '233' or p2c.categories_id = '234' or p2c.categories_id = '235' or p2c.categories_id = '236' or p2c.categories_id = '237' or p2c.categories_id = '238' or p2c.categories_id = '239' or p2c.categories_id = '240' or p2c.categories_id = '241' or p2c.categories_id = '242' or p2c.categories_id = '243' or p2c.categories_id = '244' or p2c.categories_id = '245' or p2c.categories_id = '246' or p2c.categories_id = '247' or p2c.categories_id = '248' or p2c.categories_id = '249' or p2c.categories_id = '250' or p2c.categories_id = '251' or p2c.categories_id = '252' or p2c.categories_id = '253' or p2c.categories_id = '254' or p2c.categories_id = '255' or p2c.categories_id = '256' or p2c.categories_id = '257' or p2c.categories_id = '258' or p2c.categories_id = '259' or p2c.categories_id = '260' or p2c.categories_id = '261' or p2c.categories_id = '262' or p2c.categories_id = '263' or p2c.categories_id = '264' or p2c.categories_id = '265' or p2c.categories_id = '266' or p2c.categories_id = '267' or p2c.categories_id = '268' or p2c.categories_id = '269' or p2c.categories_id = '270' or p2c.categories_id = '271' or p2c.categories_id = '272' or p2c.categories_id = '273' or p2c.categories_id = '274' or p2c.categories_id = '275' or p2c.categories_id = '276' or p2c.categories_id = '277' or p2c.categories_id = '278' or p2c.categories_id = '279' or p2c.categories_id = '280' or p2c.categories_id = '281' or p2c.categories_id = '282' or p2c.categories_id = '283' or p2c.categories_id = '284' or p2c.categories_id = '285' or p2c.categories_id = '286' or p2c.categories_id = '287' or p2c.categories_id = '288' or p2c.categories_id = '289' or p2c.categories_id = '290' or p2c.categories_id = '291' or p2c.categories_id = '292' or p2c.categories_id = '293' or p2c.categories_id = '294' or p2c.categories_id = '295' or p2c.categories_id = '296' or p2c.categories_id = '297' or p2c.categories_id = '298' or p2c.categories_id = '299' or p2c.categories_id = '300' or p2c.categories_id = '301' or p2c.categories_id = '302' or p2c.categories_id = '303' or p2c.categories_id = '304' or p2c.categories_id = '305' or p2c.categories_id = '306' or p2c.categories_id = '307' or p2c.categories_id = '308' or p2c.categories_id = '309' or p2c.categories_id = '310' or p2c.categories_id = '311' or p2c.categories_id = '312' or p2c.categories_id = '313' or p2c.categories_id = '314' or p2c.categories_id = '315' or p2c.categories_id = '316' or p2c.categories_id = '317' or p2c.categories_id = '318' or p2c.categories_id = '319' or p2c.categories_id = '320' or p2c.categories_id = '321' or p2c.categories_id = '322' or p2c.categories_id = '323' or p2c.categories_id = '324' or p2c.categories_id = '325' or p2c.categories_id = '326' or p2c.categories_id = '327' or p2c.categories_id = '328' or p2c.categories_id = '329' or p2c.categories_id = '330' or p2c.categories_id = '331' or p2c.categories_id = '332' or p2c.categories_id = '333' or p2c.categories_id = '334' or p2c.categories_id = '335' or p2c.categories_id = '336' or p2c.categories_id = '337' or p2c.categories_id = '338' or p2c.categories_id = '339' or p2c.categories_id = '340' or p2c.categories_id = '341' or p2c.categories_id = '342' or p2c.categories_id = '343' or p2c.categories_id = '344' or p2c.categories_id = '345' or p2c.categories_id = '346' or p2c.categories_id = '347' or p2c.categories_id = '348' or p2c.categories_id = '349' or p2c.categories_id = '350' or p2c.categories_id = '351' or p2c.categories_id = '352' or p2c.categories_id = '353' or p2c.categories_id = '354' or p2c.categories_id = '380' or p2c.categories_id = '381' or p2c.categories_id = '382' or p2c.categories_id = '383' or p2c.categories_id = '384' or p2c.categories_id = '385' or p2c.categories_id = '386' or p2c.categories_id = '387' or p2c.categories_id = '388' or p2c.categories_id = '389' or p2c.categories_id = '390' or p2c.categories_id = '391' or p2c.categories_id = '392' or p2c.categories_id = '393' or p2c.categories_id = '394' or p2c.categories_id = '395' or p2c.categories_id = '396' or p2c.categories_id = '397' or p2c.categories_id = '398' or p2c.categories_id = '399' or p2c.categories_id = '400' or p2c.categories_id = '401' or p2c.categories_id = '402' or p2c.categories_id = '403' or p2c.categories_id = '404' or p2c.categories_id = '405' or p2c.categories_id = '406' or p2c.categories_id = '407' or p2c.categories_id = '408' or p2c.categories_id = '409' or p2c.categories_id = '410' or p2c.categories_id = '411' or p2c.categories_id = '412' or p2c.categories_id = '413' or p2c.categories_id = '414' or p2c.categories_id = '415' or p2c.categories_id = '416' or p2c.categories_id = '417' or p2c.categories_id = '418' or p2c.categories_id = '419' or p2c.categories_id = '420' or p2c.categories_id = '421' or p2c.categories_id = '422' or p2c.categories_id = '423' or p2c.categories_id = '424' or p2c.categories_id = '425' or p2c.categories_id = '426' or p2c.categories_id = '427' or p2c.categories_id = '428' or p2c.categories_id = '429' or p2c.categories_id = '430' or p2c.categories_id = '431' or p2c.categories_id = '432' or p2c.categories_id = '433' or p2c.categories_id = '434' or p2c.categories_id = '435' or p2c.categories_id = '436' or p2c.categories_id = '437' or p2c.categories_id = '438' or p2c.categories_id = '439' or p2c.categories_id = '440' or p2c.categories_id = '441' or p2c.categories_id = '442' or p2c.categories_id = '443' or p2c.categories_id = '444' or p2c.categories_id = '445' or p2c.categories_id = '446' or p2c.categories_id = '447' or p2c.categories_id = '448' or p2c.categories_id = '449' or p2c.categories_id = '450' or p2c.categories_id = '451' or p2c.categories_id = '452' or p2c.categories_id = '453' or p2c.categories_id = '454' or p2c.categories_id = '455' or p2c.categories_id = '456' or p2c.categories_id = '457' or p2c.categories_id = '458' or p2c.categories_id = '459' or p2c.categories_id = '460' or p2c.categories_id = '461' or p2c.categories_id = '462' or p2c.categories_id = '463' or p2c.categories_id = '464' or p2c.categories_id = '465' or p2c.categories_id = '466' or p2c.categories_id = '467' or p2c.categories_id = '468' or p2c.categories_id = '469' or p2c.categories_id = '470' or p2c.categories_id = '471' or p2c.categories_id = '472' or p2c.categories_id = '473' or p2c.categories_id = '474' or p2c.categories_id = '475' or p2c.categories_id = '476' or p2c.categories_id = '477' or p2c.categories_id = '478' or p2c.categories_id = '479' or p2c.categories_id = '480' or p2c.categories_id = '481' or p2c.categories_id = '482' or p2c.categories_id = '483' or p2c.categories_id = '484' or p2c.categories_id = '485' or p2c.categories_id = '486' or p2c.categories_id = '487' or p2c.categories_id = '488' or p2c.categories_id = '489' or p2c.categories_id = '490' or p2c.categories_id = '491' or p2c.categories_id = '492' or p2c.categories_id = '493' or p2c.categories_id = '494' or p2c.categories_id = '495' or p2c.categories_id = '496' or p2c.categories_id = '497' or p2c.categories_id = '498' or p2c.categories_id = '499' or p2c.categories_id = '500' or p2c.categories_id = '501' or p2c.categories_id = '502' or p2c.categories_id = '503' or p2c.categories_id = '504' or p2c.categories_id = '505' or p2c.categories_id = '506' or p2c.categories_id = '507' or p2c.categories_id = '508' or p2c.categories_id = '509' or p2c.categories_id = '510' or p2c.categories_id = '511' or p2c.categories_id = '512' or p2c.categories_id = '513' or p2c.categories_id = '514' or p2c.categories_id = '515' or p2c.categories_id = '516' or p2c.categories_id = '517' or p2c.categories_id = '518' or p2c.categories_id = '519' or p2c.categories_id = '520' or p2c.categories_id = '521' or p2c.categories_id = '522' or p2c.categories_id = '523' or p2c.categories_id = '524' or p2c.categories_id = '525' or p2c.categories_id = '820' or p2c.categories_id = '821' or p2c.categories_id = '822' or p2c.categories_id = '823' or p2c.categories_id = '824' or p2c.categories_id = '825' or p2c.categories_id = '826' or p2c.categories_id = '827' or p2c.categories_id = '828' or p2c.categories_id = '829' or p2c.categories_id = '830' or p2c.categories_id = '831' or p2c.categories_id = '832' or p2c.categories_id = '833' or p2c.categories_id = '834' or p2c.categories_id = '835' or p2c.categories_id = '836' or p2c.categories_id = '837' or p2c.categories_id = '838' or p2c.categories_id = '839' or p2c.categories_id = '840' or p2c.categories_id = '841' or p2c.categories_id = '842' or p2c.categories_id = '843' or p2c.categories_id = '847' or p2c.categories_id = '848' or p2c.categories_id = '849' or p2c.categories_id = '850' or p2c.categories_id = '851' or p2c.categories_id = '852' or p2c.categories_id = '853' or p2c.categories_id = '854' or p2c.categories_id = '855' or p2c.categories_id = '856' or p2c.categories_id = '857' or p2c.categories_id = '858' or p2c.categories_id = '859' or p2c.categories_id = '860' or p2c.categories_id = '861' or p2c.categories_id = '862' or p2c.categories_id = '863' or p2c.categories_id = '864' or p2c.categories_id = '2022' or p2c.categories_id = '2023' or p2c.categories_id = '2024' or p2c.categories_id = '2025' or p2c.categories_id = '2026' or p2c.categories_id = '2027' or p2c.categories_id = '2028' or p2c.categories_id = '2029' or p2c.categories_id = '2076' or p2c.categories_id = '2077' or p2c.categories_id = '2078' or p2c.categories_id = '2079' or p2c.categories_id = '2080' or p2c.categories_id = '2377' or p2c.categories_id = '2378' or p2c.categories_id = '2379' or p2c.categories_id = '2453' or p2c.categories_id = '2510' or p2c.categories_id = '526' or p2c.categories_id = '527' or p2c.categories_id = '528' or p2c.categories_id = '529' or p2c.categories_id = '530' or p2c.categories_id = '531' or p2c.categories_id = '532' or p2c.categories_id = '533' or p2c.categories_id = '534' or p2c.categories_id = '535' or p2c.categories_id = '536' or p2c.categories_id = '537' or p2c.categories_id = '538' or p2c.categories_id = '539' or p2c.categories_id = '540' or p2c.categories_id = '541' or p2c.categories_id = '542' or p2c.categories_id = '543' or p2c.categories_id = '544' or p2c.categories_id = '545' or p2c.categories_id = '546' or p2c.categories_id = '547' or p2c.categories_id = '548' or p2c.categories_id = '549' or p2c.categories_id = '550' or p2c.categories_id = '551' or p2c.categories_id = '552' or p2c.categories_id = '553' or p2c.categories_id = '554' or p2c.categories_id = '555' or p2c.categories_id = '556' or p2c.categories_id = '557' or p2c.categories_id = '558' or p2c.categories_id = '559' or p2c.categories_id = '560' or p2c.categories_id = '561' or p2c.categories_id = '562' or p2c.categories_id = '563' or p2c.categories_id = '564' or p2c.categories_id = '565' or p2c.categories_id = '566' or p2c.categories_id = '567' or p2c.categories_id = '568' or p2c.categories_id = '569' or p2c.categories_id = '570' or p2c.categories_id = '571' or p2c.categories_id = '572' or p2c.categories_id = '573' or p2c.categories_id = '574' or p2c.categories_id = '575' or p2c.categories_id = '576' or p2c.categories_id = '577' or p2c.categories_id = '578' or p2c.categories_id = '579' or p2c.categories_id = '580' or p2c.categories_id = '581' or p2c.categories_id = '582' or p2c.categories_id = '583' or p2c.categories_id = '584' or p2c.categories_id = '585' or p2c.categories_id = '586' or p2c.categories_id = '587' or p2c.categories_id = '588' or p2c.categories_id = '589' or p2c.categories_id = '590' or p2c.categories_id = '591' or p2c.categories_id = '592' or p2c.categories_id = '593' or p2c.categories_id = '594' or p2c.categories_id = '595' or p2c.categories_id = '596' or p2c.categories_id = '597' or p2c.categories_id = '598' or p2c.categories_id = '599' or p2c.categories_id = '600' or p2c.categories_id = '601' or p2c.categories_id = '602' or p2c.categories_id = '603' or p2c.categories_id = '604' or p2c.categories_id = '605' or p2c.categories_id = '606' or p2c.categories_id = '607' or p2c.categories_id = '608' or p2c.categories_id = '609' or p2c.categories_id = '610' or p2c.categories_id = '611' or p2c.categories_id = '612' or p2c.categories_id = '613' or p2c.categories_id = '614' or p2c.categories_id = '615' or p2c.categories_id = '616' or p2c.categories_id = '617' or p2c.categories_id = '618' or p2c.categories_id = '619' or p2c.categories_id = '620' or p2c.categories_id = '621' or p2c.categories_id = '622' or p2c.categories_id = '623' or p2c.categories_id = '624' or p2c.categories_id = '625' or p2c.categories_id = '626' or p2c.categories_id = '627' or p2c.categories_id = '628' or p2c.categories_id = '629' or p2c.categories_id = '630' or p2c.categories_id = '631' or p2c.categories_id = '632' or p2c.categories_id = '633' or p2c.categories_id = '634' or p2c.categories_id = '635' or p2c.categories_id = '636' or p2c.categories_id = '637' or p2c.categories_id = '638' or p2c.categories_id = '639' or p2c.categories_id = '640' or p2c.categories_id = '641' or p2c.categories_id = '642' or p2c.categories_id = '643' or p2c.categories_id = '644' or p2c.categories_id = '645' or p2c.categories_id = '646' or p2c.categories_id = '647' or p2c.categories_id = '648' or p2c.categories_id = '649' or p2c.categories_id = '650' or p2c.categories_id = '651' or p2c.categories_id = '652' or p2c.categories_id = '653' or p2c.categories_id = '654' or p2c.categories_id = '655' or p2c.categories_id = '656' or p2c.categories_id = '657' or p2c.categories_id = '658' or p2c.categories_id = '659' or p2c.categories_id = '660' or p2c.categories_id = '661' or p2c.categories_id = '662' or p2c.categories_id = '663' or p2c.categories_id = '664' or p2c.categories_id = '665' or p2c.categories_id = '666' or p2c.categories_id = '667' or p2c.categories_id = '668' or p2c.categories_id = '669' or p2c.categories_id = '670' or p2c.categories_id = '671' or p2c.categories_id = '672' or p2c.categories_id = '677' or p2c.categories_id = '678' or p2c.categories_id = '679' or p2c.categories_id = '680' or p2c.categories_id = '681' or p2c.categories_id = '682' or p2c.categories_id = '683' or p2c.categories_id = '684' or p2c.categories_id = '685' or p2c.categories_id = '686' or p2c.categories_id = '687' or p2c.categories_id = '688' or p2c.categories_id = '689' or p2c.categories_id = '690' or p2c.categories_id = '691' or p2c.categories_id = '692' or p2c.categories_id = '693' or p2c.categories_id = '694' or p2c.categories_id = '695' or p2c.categories_id = '696' or p2c.categories_id = '2204' or p2c.categories_id = '2205' or p2c.categories_id = '2206' or p2c.categories_id = '2207' or p2c.categories_id = '2208' or p2c.categories_id = '2209' or p2c.categories_id = '2212' or p2c.categories_id = '2213' or p2c.categories_id = '2214' or p2c.categories_id = '2215' or p2c.categories_id = '2216' or p2c.categories_id = '2219' or p2c.categories_id = '2220' or p2c.categories_id = '2221' or p2c.categories_id = '2222' or p2c.categories_id = '2223' or p2c.categories_id = '2389' or p2c.categories_id = '2390' or p2c.categories_id = '697' or p2c.categories_id = '698' or p2c.categories_id = '699' or p2c.categories_id = '700' or p2c.categories_id = '701' or p2c.categories_id = '702' or p2c.categories_id = '703' or p2c.categories_id = '704' or p2c.categories_id = '705' or p2c.categories_id = '706' or p2c.categories_id = '707' or p2c.categories_id = '708' or p2c.categories_id = '709' or p2c.categories_id = '710' or p2c.categories_id = '711' or p2c.categories_id = '712' or p2c.categories_id = '713' or p2c.categories_id = '714' or p2c.categories_id = '715' or p2c.categories_id = '716' or p2c.categories_id = '717' or p2c.categories_id = '718' or p2c.categories_id = '719' or p2c.categories_id = '720' or p2c.categories_id = '721' or p2c.categories_id = '722' or p2c.categories_id = '723' or p2c.categories_id = '724' or p2c.categories_id = '725' or p2c.categories_id = '726' or p2c.categories_id = '727' or p2c.categories_id = '728' or p2c.categories_id = '729' or p2c.categories_id = '730' or p2c.categories_id = '731' or p2c.categories_id = '732' or p2c.categories_id = '733' or p2c.categories_id = '734' or p2c.categories_id = '735' or p2c.categories_id = '736' or p2c.categories_id = '737' or p2c.categories_id = '738' or p2c.categories_id = '739' or p2c.categories_id = '740' or p2c.categories_id = '741' or p2c.categories_id = '742' or p2c.categories_id = '743' or p2c.categories_id = '744' or p2c.categories_id = '745' or p2c.categories_id = '746' or p2c.categories_id = '747' or p2c.categories_id = '748' or p2c.categories_id = '749' or p2c.categories_id = '750' or p2c.categories_id = '751' or p2c.categories_id = '752' or p2c.categories_id = '753' or p2c.categories_id = '754' or p2c.categories_id = '755' or p2c.categories_id = '756' or p2c.categories_id = '757' or p2c.categories_id = '758' or p2c.categories_id = '759' or p2c.categories_id = '760' or p2c.categories_id = '761' or p2c.categories_id = '762' or p2c.categories_id = '763' or p2c.categories_id = '764' or p2c.categories_id = '765' or p2c.categories_id = '766' or p2c.categories_id = '767' or p2c.categories_id = '768' or p2c.categories_id = '769' or p2c.categories_id = '770' or p2c.categories_id = '771' or p2c.categories_id = '772' or p2c.categories_id = '773' or p2c.categories_id = '774' or p2c.categories_id = '775' or p2c.categories_id = '776' or p2c.categories_id = '777' or p2c.categories_id = '778' or p2c.categories_id = '779' or p2c.categories_id = '780' or p2c.categories_id = '781' or p2c.categories_id = '2061' or p2c.categories_id = '2062' or p2c.categories_id = '2063' or p2c.categories_id = '2064' or p2c.categories_id = '2065' or p2c.categories_id = '2066' or p2c.categories_id = '2067' or p2c.categories_id = '2068' or p2c.categories_id = '2069' or p2c.categories_id = '2070' or p2c.categories_id = '2071' or p2c.categories_id = '2072' or p2c.categories_id = '2157' or p2c.categories_id = '2158' or p2c.categories_id = '2159' or p2c.categories_id = '2290' or p2c.categories_id = '844' or p2c.categories_id = '845' or p2c.categories_id = '846' or p2c.categories_id = '1398' or p2c.categories_id = '1399' or p2c.categories_id = '1400' or p2c.categories_id = '1401' or p2c.categories_id = '1402' or p2c.categories_id = '1403' or p2c.categories_id = '1404' or p2c.categories_id = '1405' or p2c.categories_id = '1406' or p2c.categories_id = '1407' or p2c.categories_id = '1408' or p2c.categories_id = '1409' or p2c.categories_id = '1410' or p2c.categories_id = '1411' or p2c.categories_id = '1412' or p2c.categories_id = '1413' or p2c.categories_id = '1414' or p2c.categories_id = '1415' or p2c.categories_id = '1416' or p2c.categories_id = '1417' or p2c.categories_id = '1418' or p2c.categories_id = '2340' or p2c.categories_id = '2341' or p2c.categories_id = '2342' or p2c.categories_id = '2343' or p2c.categories_id = '2344' or p2c.categories_id = '2345' or p2c.categories_id = '2346' or p2c.categories_id = '2347' or p2c.categories_id = '2348' or p2c.categories_id = '2349' or p2c.categories_id = '2350' or p2c.categories_id = '2351' or p2c.categories_id = '1477' or p2c.categories_id = '1478' or p2c.categories_id = '1479' or p2c.categories_id = '1480' or p2c.categories_id = '1481' or p2c.categories_id = '1482' or p2c.categories_id = '1483' or p2c.categories_id = '1484' or p2c.categories_id = '1485' or p2c.categories_id = '1486' or p2c.categories_id = '1618' or p2c.categories_id = '1619' or p2c.categories_id = '1620' or p2c.categories_id = '1621' or p2c.categories_id = '1622' or p2c.categories_id = '1623' or p2c.categories_id = '2004' or p2c.categories_id = '1628' or p2c.categories_id = '1629' or p2c.categories_id = '1630' or p2c.categories_id = '1631' or p2c.categories_id = '1632' or p2c.categories_id = '1633' or p2c.categories_id = '1634' or p2c.categories_id = '1635' or p2c.categories_id = '1636' or p2c.categories_id = '1637' or p2c.categories_id = '1638' or p2c.categories_id = '1639' or p2c.categories_id = '1640' or p2c.categories_id = '1641' or p2c.categories_id = '1642' or p2c.categories_id = '1643' or p2c.categories_id = '1644' or p2c.categories_id = '1645' or p2c.categories_id = '1646' or p2c.categories_id = '1647' or p2c.categories_id = '1648' or p2c.categories_id = '1649' or p2c.categories_id = '1650' or p2c.categories_id = '1651' or p2c.categories_id = '1652' or p2c.categories_id = '1653' or p2c.categories_id = '1654' or p2c.categories_id = '1655' or p2c.categories_id = '1656' or p2c.categories_id = '1657' or p2c.categories_id = '1658' or p2c.categories_id = '1659' or p2c.categories_id = '1660' or p2c.categories_id = '1661' or p2c.categories_id = '1662' or p2c.categories_id = '1663' or p2c.categories_id = '1664' or p2c.categories_id = '1665' or p2c.categories_id = '1666' or p2c.categories_id = '1667' or p2c.categories_id = '1668' or p2c.categories_id = '1669' or p2c.categories_id = '2511' or p2c.categories_id = '2512' or p2c.categories_id = '2513' or p2c.categories_id = '2514' or p2c.categories_id = '2515' or p2c.categories_id = '1822' or p2c.categories_id = '1823' or p2c.categories_id = '2454' or p2c.categories_id = '2455' or p2c.categories_id = '2081' or p2c.categories_id = '2082' or p2c.categories_id = '2083' or p2c.categories_id = '2084' or p2c.categories_id = '2085' or p2c.categories_id = '2086' or p2c.categories_id = '2087' or p2c.categories_id = '2088' or p2c.categories_id = '2089' or p2c.categories_id = '2090' or p2c.categories_id = '2091' or p2c.categories_id = '2092' or p2c.categories_id = '2093' or p2c.categories_id = '2094' or p2c.categories_id = '2095' or p2c.categories_id = '2096' or p2c.categories_id = '2097' or p2c.categories_id = '2136' or p2c.categories_id = '2138' or p2c.categories_id = '2139' or p2c.categories_id = '2140' or p2c.categories_id = '2113' or p2c.categories_id = '2114' or p2c.categories_id = '2115' or p2c.categories_id = '2116' or p2c.categories_id = '2117' or p2c.categories_id = '2118' or p2c.categories_id = '2119' or p2c.categories_id = '2120' or p2c.categories_id = '2121' or p2c.categories_id = '2122' or p2c.categories_id = '2141' or p2c.categories_id = '2142' or p2c.categories_id = '2156' or p2c.categories_id = '2123' or p2c.categories_id = '2124' or p2c.categories_id = '2125' or p2c.categories_id = '2126' or p2c.categories_id = '2127' or p2c.categories_id = '2128' or p2c.categories_id = '2129' or p2c.categories_id = '2394' or p2c.categories_id = '2395' or p2c.categories_id = '2396' or p2c.categories_id = '2145' or p2c.categories_id = '2146' or p2c.categories_id = '2147' or p2c.categories_id = '2148' or p2c.categories_id = '2149' or p2c.categories_id = '2150' or p2c.categories_id = '2151' or p2c.categories_id = '2152' or p2c.categories_id = '2153' or p2c.categories_id = '2154' or p2c.categories_id = '2155') and ((pd.products_name like '%officejet%' or pd.products_description like '%officejet%') and (pd.products_name like '%4100%' or pd.products_description like '%4100%') ) order by pd.products_name

 

 

 

 

AND WITHOUT

 

select distinct m.manufacturers_id, p.products_id, pd.products_name, pd.products_description, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%officejet%' or pd.products_description like '%officejet%') and (pd.products_name like '%4100%' or pd.products_description like '%4100%') ) order by pd.products_name

[boxtel]

With categorie=1 it is:

 

select distinct m.manufacturers_id, p.products_id, pd.products_name, pd.products_description, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '1' or p2c.categories_id = '2' or p2c.categories_id = '3' or p2c.categories_id = '4' or p2c.categories_id = '5' or p2c.categories_id = '6' or p2c.categories_id = '7' or p2c.categories_id = '8' or p2c.categories_id = '9' or p2c.categories_id = '10' or p2c.categories_id = '11' or p2c.categories_id = '12' or p2c.categories_id = '13' or p2c.categories_id = '14' or p2c.categories_id = '15' or p2c.categories_id = '16' or p2c.categories_id = '17' or p2c.categories_id = '18' or p2c.categories_id = '22' or p2c.categories_id = '32' or p2c.categories_id = '33' or p2c.categories_id = '36' or p2c.categories_id = '37' or p2c.categories_id = '38' or p2c.categories_id = '39' or p2c.categories_id = '40' or p2c.categories_id = '41' or p2c.categories_id = '42' or p2c.categories_id = '43' or p2c.categories_id = '44' or p2c.categories_id = '45' or p2c.categories_id = '46' or p2c.categories_id = '47' or p2c.categories_id = '48' or p2c.categories_id = '51' or p2c.categories_id = '53' or p2c.categories_id = '54' or p2c.categories_id = '55' or p2c.categories_id = '56' or p2c.categories_id = '57' or p2c.categories_id = '58' or p2c.categories_id = '59' or p2c.categories_id = '60' or p2c.categories_id = '61' or p2c.categories_id = '62' or p2c.categories_id = '63' or p2c.categories_id = '64' or p2c.categories_id = '65' or p2c.categories_id = '66' or p2c.categories_id = '67' or p2c.categories_id = '68' or p2c.categories_id = '69' or p2c.categories_id = '70' or p2c.categories_id = '71' or p2c.categories_id = '72' or p2c.categories_id = '73' or p2c.categories_id = '74' or p2c.categories_id = '75' or p2c.categories_id = '76' or p2c.categories_id = '77' or p2c.categories_id = '78' or p2c.categories_id = '79' or p2c.categories_id = '80' or p2c.categories_id = '81' or p2c.categories_id = '82' or p2c.categories_id = '83' or p2c.categories_id = '84' or p2c.categories_id = '85' or p2c.categories_id = '86' or p2c.categories_id = '87' or p2c.categories_id = '88' or p2c.categories_id = '89' or p2c.categories_id = '90' or p2c.categories_id = '91' or p2c.categories_id = '92' or p2c.categories_id = '93' or p2c.categories_id = '94' or p2c.categories_id = '95' or p2c.categories_id = '96' or p2c.categories_id = '97' or p2c.categories_id = '98' or p2c.categories_id = '99' or p2c.categories_id = '100' or p2c.categories_id = '101' or p2c.categories_id = '102' or p2c.categories_id = '103' or p2c.categories_id = '104' or p2c.categories_id = '782' or p2c.categories_id = '783' or p2c.categories_id = '784' or p2c.categories_id = '785' or p2c.categories_id = '786' or p2c.categories_id = '787' or p2c.categories_id = '788' or p2c.categories_id = '789' or p2c.categories_id = '790' or p2c.categories_id = '791' or p2c.categories_id = '792' or p2c.categories_id = '793' or p2c.categories_id = '794' or p2c.categories_id = '795' or p2c.categories_id = '796' or p2c.categories_id = '797' or p2c.categories_id = '798' or p2c.categories_id = '799' or p2c.categories_id = '800' or p2c.categories_id = '801' or p2c.categories_id = '802' or p2c.categories_id = '803' or p2c.categories_id = '804' or p2c.categories_id = '805' or p2c.categories_id = '806' or p2c.categories_id = '807' or p2c.categories_id = '808' or p2c.categories_id = '809' or p2c.categories_id = '810' or p2c.categories_id = '811' or p2c.categories_id = '812' or p2c.categories_id = '813' or p2c.categories_id = '814' or p2c.categories_id = '815' or p2c.categories_id = '816' or p2c.categories_id = '817' or p2c.categories_id = '818' or p2c.categories_id = '819' or p2c.categories_id = '2030' or p2c.categories_id = '2031' or p2c.categories_id = '2032' or p2c.categories_id = '2033' or p2c.categories_id = '2034' or p2c.categories_id = '2035' or p2c.categories_id = '2036' or p2c.categories_id = '2037' or p2c.categories_id = '2038' or p2c.categories_id = '2039' or p2c.categories_id = '2048' or p2c.categories_id = '2049' or p2c.categories_id = '2050' or p2c.categories_id = '2051' or p2c.categories_id = '2052' or p2c.categories_id = '2053' or p2c.categories_id = '2054' or p2c.categories_id = '2143' or p2c.categories_id = '2144' or p2c.categories_id = '2210' or p2c.categories_id = '2211' or p2c.categories_id = '2224' or p2c.categories_id = '2225' or p2c.categories_id = '2238' or p2c.categories_id = '2239' or p2c.categories_id = '2240' or p2c.categories_id = '2352' or p2c.categories_id = '2440' or p2c.categories_id = '2441' or p2c.categories_id = '2459' or p2c.categories_id = '2460' or p2c.categories_id = '2461' or p2c.categories_id = '2462' or p2c.categories_id = '2463' or p2c.categories_id = '2464' or p2c.categories_id = '2465' or p2c.categories_id = '2466' or p2c.categories_id = '2467' or p2c.categories_id = '2468' or p2c.categories_id = '105' or p2c.categories_id = '106' or p2c.categories_id = '107' or p2c.categories_id = '108' or p2c.categories_id = '109' or p2c.categories_id = '110' or p2c.categories_id = '111' or p2c.categories_id = '112' or p2c.categories_id = '113' or p2c.categories_id = '114' or p2c.categories_id = '115' or p2c.categories_id = '116' or p2c.categories_id = '117' or p2c.categories_id = '118' or p2c.categories_id = '119' or p2c.categories_id = '120' or p2c.categories_id = '121' or p2c.categories_id = '122' or p2c.categories_id = '123' or p2c.categories_id = '124' or p2c.categories_id = '125' or p2c.categories_id = '126' or p2c.categories_id = '127' or p2c.categories_id = '128' or p2c.categories_id = '129' or p2c.categories_id = '130' or p2c.categories_id = '131' or p2c.categories_id = '132' or p2c.categories_id = '133' or p2c.categories_id = '134' or p2c.categories_id = '135' or p2c.categories_id = '136' or p2c.categories_id = '137' or p2c.categories_id = '138' or p2c.categories_id = '139' or p2c.categories_id = '140' or p2c.categories_id = '141' or p2c.categories_id = '142' or p2c.categories_id = '143' or p2c.categories_id = '144' or p2c.categories_id = '145' or p2c.categories_id = '146' or p2c.categories_id = '147' or p2c.categories_id = '148' or p2c.categories_id = '149' or p2c.categories_id = '150' or p2c.categories_id = '151' or p2c.categories_id = '152' or p2c.categories_id = '153' or p2c.categories_id = '154' or p2c.categories_id = '155' or p2c.categories_id = '156' or p2c.categories_id = '157' or p2c.categories_id = '158' or p2c.categories_id = '159' or p2c.categories_id = '160' or p2c.categories_id = '161' or p2c.categories_id = '162' or p2c.categories_id = '163' or p2c.categories_id = '164' or p2c.categories_id = '165' or p2c.categories_id = '166' or p2c.categories_id = '167' or p2c.categories_id = '168' or p2c.categories_id = '169' or p2c.categories_id = '170' or p2c.categories_id = '171' or p2c.categories_id = '172' or p2c.categories_id = '213' or p2c.categories_id = '214' or p2c.categories_id = '215' or p2c.categories_id = '216' or p2c.categories_id = '217' or p2c.categories_id = '218' or p2c.categories_id = '219' or p2c.categories_id = '220' or p2c.categories_id = '221' or p2c.categories_id = '222' or p2c.categories_id = '223' or p2c.categories_id = '224' or p2c.categories_id = '225' or p2c.categories_id = '226' or p2c.categories_id = '227' or p2c.categories_id = '228' or p2c.categories_id = '229' or p2c.categories_id = '230' or p2c.categories_id = '231' or p2c.categories_id = '232' or p2c.categories_id = '233' or p2c.categories_id = '234' or p2c.categories_id = '235' or p2c.categories_id = '236' or p2c.categories_id = '237' or p2c.categories_id = '238' or p2c.categories_id = '239' or p2c.categories_id = '240' or p2c.categories_id = '241' or p2c.categories_id = '242' or p2c.categories_id = '243' or p2c.categories_id = '244' or p2c.categories_id = '245' or p2c.categories_id = '246' or p2c.categories_id = '247' or p2c.categories_id = '248' or p2c.categories_id = '249' or p2c.categories_id = '250' or p2c.categories_id = '251' or p2c.categories_id = '252' or p2c.categories_id = '253' or p2c.categories_id = '254' or p2c.categories_id = '255' or p2c.categories_id = '256' or p2c.categories_id = '257' or p2c.categories_id = '258' or p2c.categories_id = '259' or p2c.categories_id = '260' or p2c.categories_id = '261' or p2c.categories_id = '262' or p2c.categories_id = '263' or p2c.categories_id = '264' or p2c.categories_id = '265' or p2c.categories_id = '266' or p2c.categories_id = '267' or p2c.categories_id = '268' or p2c.categories_id = '269' or p2c.categories_id = '270' or p2c.categories_id = '271' or p2c.categories_id = '272' or p2c.categories_id = '273' or p2c.categories_id = '274' or p2c.categories_id = '275' or p2c.categories_id = '276' or p2c.categories_id = '277' or p2c.categories_id = '278' or p2c.categories_id = '279' or p2c.categories_id = '280' or p2c.categories_id = '281' or p2c.categories_id = '282' or p2c.categories_id = '283' or p2c.categories_id = '284' or p2c.categories_id = '285' or p2c.categories_id = '286' or p2c.categories_id = '287' or p2c.categories_id = '288' or p2c.categories_id = '289' or p2c.categories_id = '290' or p2c.categories_id = '291' or p2c.categories_id = '292' or p2c.categories_id = '293' or p2c.categories_id = '294' or p2c.categories_id = '295' or p2c.categories_id = '296' or p2c.categories_id = '297' or p2c.categories_id = '298' or p2c.categories_id = '299' or p2c.categories_id = '300' or p2c.categories_id = '301' or p2c.categories_id = '302' or p2c.categories_id = '303' or p2c.categories_id = '304' or p2c.categories_id = '305' or p2c.categories_id = '306' or p2c.categories_id = '307' or p2c.categories_id = '308' or p2c.categories_id = '309' or p2c.categories_id = '310' or p2c.categories_id = '311' or p2c.categories_id = '312' or p2c.categories_id = '313' or p2c.categories_id = '314' or p2c.categories_id = '315' or p2c.categories_id = '316' or p2c.categories_id = '317' or p2c.categories_id = '318' or p2c.categories_id = '319' or p2c.categories_id = '320' or p2c.categories_id = '321' or p2c.categories_id = '322' or p2c.categories_id = '323' or p2c.categories_id = '324' or p2c.categories_id = '325' or p2c.categories_id = '326' or p2c.categories_id = '327' or p2c.categories_id = '328' or p2c.categories_id = '329' or p2c.categories_id = '330' or p2c.categories_id = '331' or p2c.categories_id = '332' or p2c.categories_id = '333' or p2c.categories_id = '334' or p2c.categories_id = '335' or p2c.categories_id = '336' or p2c.categories_id = '337' or p2c.categories_id = '338' or p2c.categories_id = '339' or p2c.categories_id = '340' or p2c.categories_id = '341' or p2c.categories_id = '342' or p2c.categories_id = '343' or p2c.categories_id = '344' or p2c.categories_id = '345' or p2c.categories_id = '346' or p2c.categories_id = '347' or p2c.categories_id = '348' or p2c.categories_id = '349' or p2c.categories_id = '350' or p2c.categories_id = '351' or p2c.categories_id = '352' or p2c.categories_id = '353' or p2c.categories_id = '354' or p2c.categories_id = '380' or p2c.categories_id = '381' or p2c.categories_id = '382' or p2c.categories_id = '383' or p2c.categories_id = '384' or p2c.categories_id = '385' or p2c.categories_id = '386' or p2c.categories_id = '387' or p2c.categories_id = '388' or p2c.categories_id = '389' or p2c.categories_id = '390' or p2c.categories_id = '391' or p2c.categories_id = '392' or p2c.categories_id = '393' or p2c.categories_id = '394' or p2c.categories_id = '395' or p2c.categories_id = '396' or p2c.categories_id = '397' or p2c.categories_id = '398' or p2c.categories_id = '399' or p2c.categories_id = '400' or p2c.categories_id = '401' or p2c.categories_id = '402' or p2c.categories_id = '403' or p2c.categories_id = '404' or p2c.categories_id = '405' or p2c.categories_id = '406' or p2c.categories_id = '407' or p2c.categories_id = '408' or p2c.categories_id = '409' or p2c.categories_id = '410' or p2c.categories_id = '411' or p2c.categories_id = '412' or p2c.categories_id = '413' or p2c.categories_id = '414' or p2c.categories_id = '415' or p2c.categories_id = '416' or p2c.categories_id = '417' or p2c.categories_id = '418' or p2c.categories_id = '419' or p2c.categories_id = '420' or p2c.categories_id = '421' or p2c.categories_id = '422' or p2c.categories_id = '423' or p2c.categories_id = '424' or p2c.categories_id = '425' or p2c.categories_id = '426' or p2c.categories_id = '427' or p2c.categories_id = '428' or p2c.categories_id = '429' or p2c.categories_id = '430' or p2c.categories_id = '431' or p2c.categories_id = '432' or p2c.categories_id = '433' or p2c.categories_id = '434' or p2c.categories_id = '435' or p2c.categories_id = '436' or p2c.categories_id = '437' or p2c.categories_id = '438' or p2c.categories_id = '439' or p2c.categories_id = '440' or p2c.categories_id = '441' or p2c.categories_id = '442' or p2c.categories_id = '443' or p2c.categories_id = '444' or p2c.categories_id = '445' or p2c.categories_id = '446' or p2c.categories_id = '447' or p2c.categories_id = '448' or p2c.categories_id = '449' or p2c.categories_id = '450' or p2c.categories_id = '451' or p2c.categories_id = '452' or p2c.categories_id = '453' or p2c.categories_id = '454' or p2c.categories_id = '455' or p2c.categories_id = '456' or p2c.categories_id = '457' or p2c.categories_id = '458' or p2c.categories_id = '459' or p2c.categories_id = '460' or p2c.categories_id = '461' or p2c.categories_id = '462' or p2c.categories_id = '463' or p2c.categories_id = '464' or p2c.categories_id = '465' or p2c.categories_id = '466' or p2c.categories_id = '467' or p2c.categories_id = '468' or p2c.categories_id = '469' or p2c.categories_id = '470' or p2c.categories_id = '471' or p2c.categories_id = '472' or p2c.categories_id = '473' or p2c.categories_id = '474' or p2c.categories_id = '475' or p2c.categories_id = '476' or p2c.categories_id = '477' or p2c.categories_id = '478' or p2c.categories_id = '479' or p2c.categories_id = '480' or p2c.categories_id = '481' or p2c.categories_id = '482' or p2c.categories_id = '483' or p2c.categories_id = '484' or p2c.categories_id = '485' or p2c.categories_id = '486' or p2c.categories_id = '487' or p2c.categories_id = '488' or p2c.categories_id = '489' or p2c.categories_id = '490' or p2c.categories_id = '491' or p2c.categories_id = '492' or p2c.categories_id = '493' or p2c.categories_id = '494' or p2c.categories_id = '495' or p2c.categories_id = '496' or p2c.categories_id = '497' or p2c.categories_id = '498' or p2c.categories_id = '499' or p2c.categories_id = '500' or p2c.categories_id = '501' or p2c.categories_id = '502' or p2c.categories_id = '503' or p2c.categories_id = '504' or p2c.categories_id = '505' or p2c.categories_id = '506' or p2c.categories_id = '507' or p2c.categories_id = '508' or p2c.categories_id = '509' or p2c.categories_id = '510' or p2c.categories_id = '511' or p2c.categories_id = '512' or p2c.categories_id = '513' or p2c.categories_id = '514' or p2c.categories_id = '515' or p2c.categories_id = '516' or p2c.categories_id = '517' or p2c.categories_id = '518' or p2c.categories_id = '519' or p2c.categories_id = '520' or p2c.categories_id = '521' or p2c.categories_id = '522' or p2c.categories_id = '523' or p2c.categories_id = '524' or p2c.categories_id = '525' or p2c.categories_id = '820' or p2c.categories_id = '821' or p2c.categories_id = '822' or p2c.categories_id = '823' or p2c.categories_id = '824' or p2c.categories_id = '825' or p2c.categories_id = '826' or p2c.categories_id = '827' or p2c.categories_id = '828' or p2c.categories_id = '829' or p2c.categories_id = '830' or p2c.categories_id = '831' or p2c.categories_id = '832' or p2c.categories_id = '833' or p2c.categories_id = '834' or p2c.categories_id = '835' or p2c.categories_id = '836' or p2c.categories_id = '837' or p2c.categories_id = '838' or p2c.categories_id = '839' or p2c.categories_id = '840' or p2c.categories_id = '841' or p2c.categories_id = '842' or p2c.categories_id = '843' or p2c.categories_id = '847' or p2c.categories_id = '848' or p2c.categories_id = '849' or p2c.categories_id = '850' or p2c.categories_id = '851' or p2c.categories_id = '852' or p2c.categories_id = '853' or p2c.categories_id = '854' or p2c.categories_id = '855' or p2c.categories_id = '856' or p2c.categories_id = '857' or p2c.categories_id = '858' or p2c.categories_id = '859' or p2c.categories_id = '860' or p2c.categories_id = '861' or p2c.categories_id = '862' or p2c.categories_id = '863' or p2c.categories_id = '864' or p2c.categories_id = '2022' or p2c.categories_id = '2023' or p2c.categories_id = '2024' or p2c.categories_id = '2025' or p2c.categories_id = '2026' or p2c.categories_id = '2027' or p2c.categories_id = '2028' or p2c.categories_id = '2029' or p2c.categories_id = '2076' or p2c.categories_id = '2077' or p2c.categories_id = '2078' or p2c.categories_id = '2079' or p2c.categories_id = '2080' or p2c.categories_id = '2377' or p2c.categories_id = '2378' or p2c.categories_id = '2379' or p2c.categories_id = '2453' or p2c.categories_id = '2510' or p2c.categories_id = '526' or p2c.categories_id = '527' or p2c.categories_id = '528' or p2c.categories_id = '529' or p2c.categories_id = '530' or p2c.categories_id = '531' or p2c.categories_id = '532' or p2c.categories_id = '533' or p2c.categories_id = '534' or p2c.categories_id = '535' or p2c.categories_id = '536' or p2c.categories_id = '537' or p2c.categories_id = '538' or p2c.categories_id = '539' or p2c.categories_id = '540' or p2c.categories_id = '541' or p2c.categories_id = '542' or p2c.categories_id = '543' or p2c.categories_id = '544' or p2c.categories_id = '545' or p2c.categories_id = '546' or p2c.categories_id = '547' or p2c.categories_id = '548' or p2c.categories_id = '549' or p2c.categories_id = '550' or p2c.categories_id = '551' or p2c.categories_id = '552' or p2c.categories_id = '553' or p2c.categories_id = '554' or p2c.categories_id = '555' or p2c.categories_id = '556' or p2c.categories_id = '557' or p2c.categories_id = '558' or p2c.categories_id = '559' or p2c.categories_id = '560' or p2c.categories_id = '561' or p2c.categories_id = '562' or p2c.categories_id = '563' or p2c.categories_id = '564' or p2c.categories_id = '565' or p2c.categories_id = '566' or p2c.categories_id = '567' or p2c.categories_id = '568' or p2c.categories_id = '569' or p2c.categories_id = '570' or p2c.categories_id = '571' or p2c.categories_id = '572' or p2c.categories_id = '573' or p2c.categories_id = '574' or p2c.categories_id = '575' or p2c.categories_id = '576' or p2c.categories_id = '577' or p2c.categories_id = '578' or p2c.categories_id = '579' or p2c.categories_id = '580' or p2c.categories_id = '581' or p2c.categories_id = '582' or p2c.categories_id = '583' or p2c.categories_id = '584' or p2c.categories_id = '585' or p2c.categories_id = '586' or p2c.categories_id = '587' or p2c.categories_id = '588' or p2c.categories_id = '589' or p2c.categories_id = '590' or p2c.categories_id = '591' or p2c.categories_id = '592' or p2c.categories_id = '593' or p2c.categories_id = '594' or p2c.categories_id = '595' or p2c.categories_id = '596' or p2c.categories_id = '597' or p2c.categories_id = '598' or p2c.categories_id = '599' or p2c.categories_id = '600' or p2c.categories_id = '601' or p2c.categories_id = '602' or p2c.categories_id = '603' or p2c.categories_id = '604' or p2c.categories_id = '605' or p2c.categories_id = '606' or p2c.categories_id = '607' or p2c.categories_id = '608' or p2c.categories_id = '609' or p2c.categories_id = '610' or p2c.categories_id = '611' or p2c.categories_id = '612' or p2c.categories_id = '613' or p2c.categories_id = '614' or p2c.categories_id = '615' or p2c.categories_id = '616' or p2c.categories_id = '617' or p2c.categories_id = '618' or p2c.categories_id = '619' or p2c.categories_id = '620' or p2c.categories_id = '621' or p2c.categories_id = '622' or p2c.categories_id = '623' or p2c.categories_id = '624' or p2c.categories_id = '625' or p2c.categories_id = '626' or p2c.categories_id = '627' or p2c.categories_id = '628' or p2c.categories_id = '629' or p2c.categories_id = '630' or p2c.categories_id = '631' or p2c.categories_id = '632' or p2c.categories_id = '633' or p2c.categories_id = '634' or p2c.categories_id = '635' or p2c.categories_id = '636' or p2c.categories_id = '637' or p2c.categories_id = '638' or p2c.categories_id = '639' or p2c.categories_id = '640' or p2c.categories_id = '641' or p2c.categories_id = '642' or p2c.categories_id = '643' or p2c.categories_id = '644' or p2c.categories_id = '645' or p2c.categories_id = '646' or p2c.categories_id = '647' or p2c.categories_id = '648' or p2c.categories_id = '649' or p2c.categories_id = '650' or p2c.categories_id = '651' or p2c.categories_id = '652' or p2c.categories_id = '653' or p2c.categories_id = '654' or p2c.categories_id = '655' or p2c.categories_id = '656' or p2c.categories_id = '657' or p2c.categories_id = '658' or p2c.categories_id = '659' or p2c.categories_id = '660' or p2c.categories_id = '661' or p2c.categories_id = '662' or p2c.categories_id = '663' or p2c.categories_id = '664' or p2c.categories_id = '665' or p2c.categories_id = '666' or p2c.categories_id = '667' or p2c.categories_id = '668' or p2c.categories_id = '669' or p2c.categories_id = '670' or p2c.categories_id = '671' or p2c.categories_id = '672' or p2c.categories_id = '677' or p2c.categories_id = '678' or p2c.categories_id = '679' or p2c.categories_id = '680' or p2c.categories_id = '681' or p2c.categories_id = '682' or p2c.categories_id = '683' or p2c.categories_id = '684' or p2c.categories_id = '685' or p2c.categories_id = '686' or p2c.categories_id = '687' or p2c.categories_id = '688' or p2c.categories_id = '689' or p2c.categories_id = '690' or p2c.categories_id = '691' or p2c.categories_id = '692' or p2c.categories_id = '693' or p2c.categories_id = '694' or p2c.categories_id = '695' or p2c.categories_id = '696' or p2c.categories_id = '2204' or p2c.categories_id = '2205' or p2c.categories_id = '2206' or p2c.categories_id = '2207' or p2c.categories_id = '2208' or p2c.categories_id = '2209' or p2c.categories_id = '2212' or p2c.categories_id = '2213' or p2c.categories_id = '2214' or p2c.categories_id = '2215' or p2c.categories_id = '2216' or p2c.categories_id = '2219' or p2c.categories_id = '2220' or p2c.categories_id = '2221' or p2c.categories_id = '2222' or p2c.categories_id = '2223' or p2c.categories_id = '2389' or p2c.categories_id = '2390' or p2c.categories_id = '697' or p2c.categories_id = '698' or p2c.categories_id = '699' or p2c.categories_id = '700' or p2c.categories_id = '701' or p2c.categories_id = '702' or p2c.categories_id = '703' or p2c.categories_id = '704' or p2c.categories_id = '705' or p2c.categories_id = '706' or p2c.categories_id = '707' or p2c.categories_id = '708' or p2c.categories_id = '709' or p2c.categories_id = '710' or p2c.categories_id = '711' or p2c.categories_id = '712' or p2c.categories_id = '713' or p2c.categories_id = '714' or p2c.categories_id = '715' or p2c.categories_id = '716' or p2c.categories_id = '717' or p2c.categories_id = '718' or p2c.categories_id = '719' or p2c.categories_id = '720' or p2c.categories_id = '721' or p2c.categories_id = '722' or p2c.categories_id = '723' or p2c.categories_id = '724' or p2c.categories_id = '725' or p2c.categories_id = '726' or p2c.categories_id = '727' or p2c.categories_id = '728' or p2c.categories_id = '729' or p2c.categories_id = '730' or p2c.categories_id = '731' or p2c.categories_id = '732' or p2c.categories_id = '733' or p2c.categories_id = '734' or p2c.categories_id = '735' or p2c.categories_id = '736' or p2c.categories_id = '737' or p2c.categories_id = '738' or p2c.categories_id = '739' or p2c.categories_id = '740' or p2c.categories_id = '741' or p2c.categories_id = '742' or p2c.categories_id = '743' or p2c.categories_id = '744' or p2c.categories_id = '745' or p2c.categories_id = '746' or p2c.categories_id = '747' or p2c.categories_id = '748' or p2c.categories_id = '749' or p2c.categories_id = '750' or p2c.categories_id = '751' or p2c.categories_id = '752' or p2c.categories_id = '753' or p2c.categories_id = '754' or p2c.categories_id = '755' or p2c.categories_id = '756' or p2c.categories_id = '757' or p2c.categories_id = '758' or p2c.categories_id = '759' or p2c.categories_id = '760' or p2c.categories_id = '761' or p2c.categories_id = '762' or p2c.categories_id = '763' or p2c.categories_id = '764' or p2c.categories_id = '765' or p2c.categories_id = '766' or p2c.categories_id = '767' or p2c.categories_id = '768' or p2c.categories_id = '769' or p2c.categories_id = '770' or p2c.categories_id = '771' or p2c.categories_id = '772' or p2c.categories_id = '773' or p2c.categories_id = '774' or p2c.categories_id = '775' or p2c.categories_id = '776' or p2c.categories_id = '777' or p2c.categories_id = '778' or p2c.categories_id = '779' or p2c.categories_id = '780' or p2c.categories_id = '781' or p2c.categories_id = '2061' or p2c.categories_id = '2062' or p2c.categories_id = '2063' or p2c.categories_id = '2064' or p2c.categories_id = '2065' or p2c.categories_id = '2066' or p2c.categories_id = '2067' or p2c.categories_id = '2068' or p2c.categories_id = '2069' or p2c.categories_id = '2070' or p2c.categories_id = '2071' or p2c.categories_id = '2072' or p2c.categories_id = '2157' or p2c.categories_id = '2158' or p2c.categories_id = '2159' or p2c.categories_id = '2290' or p2c.categories_id = '844' or p2c.categories_id = '845' or p2c.categories_id = '846' or p2c.categories_id = '1398' or p2c.categories_id = '1399' or p2c.categories_id = '1400' or p2c.categories_id = '1401' or p2c.categories_id = '1402' or p2c.categories_id = '1403' or p2c.categories_id = '1404' or p2c.categories_id = '1405' or p2c.categories_id = '1406' or p2c.categories_id = '1407' or p2c.categories_id = '1408' or p2c.categories_id = '1409' or p2c.categories_id = '1410' or p2c.categories_id = '1411' or p2c.categories_id = '1412' or p2c.categories_id = '1413' or p2c.categories_id = '1414' or p2c.categories_id = '1415' or p2c.categories_id = '1416' or p2c.categories_id = '1417' or p2c.categories_id = '1418' or p2c.categories_id = '2340' or p2c.categories_id = '2341' or p2c.categories_id = '2342' or p2c.categories_id = '2343' or p2c.categories_id = '2344' or p2c.categories_id = '2345' or p2c.categories_id = '2346' or p2c.categories_id = '2347' or p2c.categories_id = '2348' or p2c.categories_id = '2349' or p2c.categories_id = '2350' or p2c.categories_id = '2351' or p2c.categories_id = '1477' or p2c.categories_id = '1478' or p2c.categories_id = '1479' or p2c.categories_id = '1480' or p2c.categories_id = '1481' or p2c.categories_id = '1482' or p2c.categories_id = '1483' or p2c.categories_id = '1484' or p2c.categories_id = '1485' or p2c.categories_id = '1486' or p2c.categories_id = '1618' or p2c.categories_id = '1619' or p2c.categories_id = '1620' or p2c.categories_id = '1621' or p2c.categories_id = '1622' or p2c.categories_id = '1623' or p2c.categories_id = '2004' or p2c.categories_id = '1628' or p2c.categories_id = '1629' or p2c.categories_id = '1630' or p2c.categories_id = '1631' or p2c.categories_id = '1632' or p2c.categories_id = '1633' or p2c.categories_id = '1634' or p2c.categories_id = '1635' or p2c.categories_id = '1636' or p2c.categories_id = '1637' or p2c.categories_id = '1638' or p2c.categories_id = '1639' or p2c.categories_id = '1640' or p2c.categories_id = '1641' or p2c.categories_id = '1642' or p2c.categories_id = '1643' or p2c.categories_id = '1644' or p2c.categories_id = '1645' or p2c.categories_id = '1646' or p2c.categories_id = '1647' or p2c.categories_id = '1648' or p2c.categories_id = '1649' or p2c.categories_id = '1650' or p2c.categories_id = '1651' or p2c.categories_id = '1652' or p2c.categories_id = '1653' or p2c.categories_id = '1654' or p2c.categories_id = '1655' or p2c.categories_id = '1656' or p2c.categories_id = '1657' or p2c.categories_id = '1658' or p2c.categories_id = '1659' or p2c.categories_id = '1660' or p2c.categories_id = '1661' or p2c.categories_id = '1662' or p2c.categories_id = '1663' or p2c.categories_id = '1664' or p2c.categories_id = '1665' or p2c.categories_id = '1666' or p2c.categories_id = '1667' or p2c.categories_id = '1668' or p2c.categories_id = '1669' or p2c.categories_id = '2511' or p2c.categories_id = '2512' or p2c.categories_id = '2513' or p2c.categories_id = '2514' or p2c.categories_id = '2515' or p2c.categories_id = '1822' or p2c.categories_id = '1823' or p2c.categories_id = '2454' or p2c.categories_id = '2455' or p2c.categories_id = '2081' or p2c.categories_id = '2082' or p2c.categories_id = '2083' or p2c.categories_id = '2084' or p2c.categories_id = '2085' or p2c.categories_id = '2086' or p2c.categories_id = '2087' or p2c.categories_id = '2088' or p2c.categories_id = '2089' or p2c.categories_id = '2090' or p2c.categories_id = '2091' or p2c.categories_id = '2092' or p2c.categories_id = '2093' or p2c.categories_id = '2094' or p2c.categories_id = '2095' or p2c.categories_id = '2096' or p2c.categories_id = '2097' or p2c.categories_id = '2136' or p2c.categories_id = '2138' or p2c.categories_id = '2139' or p2c.categories_id = '2140' or p2c.categories_id = '2113' or p2c.categories_id = '2114' or p2c.categories_id = '2115' or p2c.categories_id = '2116' or p2c.categories_id = '2117' or p2c.categories_id = '2118' or p2c.categories_id = '2119' or p2c.categories_id = '2120' or p2c.categories_id = '2121' or p2c.categories_id = '2122' or p2c.categories_id = '2141' or p2c.categories_id = '2142' or p2c.categories_id = '2156' or p2c.categories_id = '2123' or p2c.categories_id = '2124' or p2c.categories_id = '2125' or p2c.categories_id = '2126' or p2c.categories_id = '2127' or p2c.categories_id = '2128' or p2c.categories_id = '2129' or p2c.categories_id = '2394' or p2c.categories_id = '2395' or p2c.categories_id = '2396' or p2c.categories_id = '2145' or p2c.categories_id = '2146' or p2c.categories_id = '2147' or p2c.categories_id = '2148' or p2c.categories_id = '2149' or p2c.categories_id = '2150' or p2c.categories_id = '2151' or p2c.categories_id = '2152' or p2c.categories_id = '2153' or p2c.categories_id = '2154' or p2c.categories_id = '2155') and ((pd.products_name like '%officejet%' or pd.products_description like '%officejet%') and (pd.products_name like '%4100%' or pd.products_description like '%4100%') ) order by pd.products_name

AND WITHOUT

 

select distinct m.manufacturers_id, p.products_id, pd.products_name, pd.products_description, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%officejet%' or pd.products_description like '%officejet%') and (pd.products_name like '%4100%' or pd.products_description like '%4100%') ) order by pd.products_name

 

well, there is a slight difference.

[boxtel]

well, there is a slight difference.

 

you have more sub-categories than wallmart, tesco and carrefour combined.

 

Maybe this site can help you better :http://www.cray.com/

[Guest]

funny =)

 

Well, so....there's no hope for this?

is there any way to speed it up? i mean it can search the whole shop NP, but when searching on categorie it has problems :/

 

it are only 5 top categorie's :)

[boxtel]

funny =)

 

Well, so....there's no hope for this?

is there any way to speed it up? i mean it can search the whole shop NP, but when searching on categorie it has problems :/

 

it are only 5 top categorie's :)

 

well, no hope, with so many sub-categories, what were you thinking ...and what are you selling?

 

I would say, either reconsider your category policy or get rid of this "search in subcategories" stuff and simply search store-wide.

[♥bruyndoncx]

if you don't have any sub-subcategories, it is possible to re-write the logic to select only products in a category for which the parent is the selected category

[♥bruyndoncx]

BTW I agree with Amanda, you should probably rethink your category strategy but in the mean time, you probably can lessen the pain by adding the following index:

 

create unique index idx_p2c_catprod on PRODUCTS_TO_CATEGORIES (categories_id, products_id)

 

HTH

[boxtel]

BTW I agree with Amanda, you should probably rethink your category strategy but in the mean time, you probably can lessen the pain by adding the following index:

 

create unique index idx_p2c_catprod on PRODUCTS_TO_CATEGORIES (categories_id, products_id)

 

HTH

 

Well, as far as I know with my limited db skills, both fields are keys in that table already and as such are indexed by default. But maybe Monika can answer that one better.

[Guest]

thanx for the thinking, but i really need this structure :)

 

I have another theorie, i can add all categorie's with a batch and make sure like categorie 1's deepest link (where all the products are in) is ID 1-1000

and like categorie 2 1001-2000 etc etc

 

so i can make a new query that well look between them......

 

its a little more work with the update batch, but it should be np.....

[♥bruyndoncx]

Amanda,

 

You are correct - in this case the additional index is not used, however it is used if you browse by categories. That is the reason why I have it added to my store. Adding the index on both fields ensures that all necessary data is retrieved from the index and a roundtrip to the table data is not needed.

 

This is the explain output on my site for the problem query. basically it shows as far as I can tell that the query is optimized because is uses index and eq_ref, only the link to the specials table might be a bit problematic as it is not optimized but a full table scan is used.

 

I'll ask Monika to have a look, hopefully she's as versed in mysql performance as Oracle.

 

1	SIMPLE	p2c	index	PRIMARY,idx_cat2prod	PRIMARY	8	NULL	3282	Using where; Using index; Using temporary; Using filesort
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	4	osc_prod.p2c.categories_id	1	Using index
1	SIMPLE	pd	eq_ref	PRIMARY	PRIMARY	8	osc_prod.p2c.products_id,const	1	Using where
1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	278	
1	SIMPLE	p	eq_ref	PRIMARY,ActiveProducts	PRIMARY	4	osc_prod.pd.products_id	1	Using where
1	SIMPLE	m	eq_ref	PRIMARY	PRIMARY	4	osc_prod.p.manufacturers_id	1	Using index

 

Carine

[♥Monika in Germany]

Hi, yes the primary key of the products_to_categories table covers that combination index already by default ...

 

THAT SAID:

I've had tremendous results by dropping and recreating this combined index !!! Definitely worth a try.

 

Also, I would make sure in products table products_status is indexed also.