I have the following mysql query and im totally lost how to optimize this, can anyone please try to point me in the right direction.
SELECT COUNT(xcart_products.productid) FROM xcart_pricing, xcart_products
LEFT JOIN xcart_quick_flags ON xcart_quick_flags.productid =
xcart_products.productid LEFT JOIN xcart_product_memberships ON
xcart_product_memberships.productid = xcart_products.productid LEFT JOIN
xcart_extra_field_values as EF_SORT ON xcart_products.productid =
EF_SORT.productid and EF_SORT.fieldid = '1' LEFT JOIN xcart_extra_field_values
ON xcart_products.productid = xcart_extra_field_values.productid and
xcart_extra_field_values.fieldid = '1' INNER JOIN xcart_quick_prices ON
xcart_quick_prices.productid = xcart_products.productid AND
xcart_quick_prices.membershipid = '0' LEFT JOIN xcart_variants ON
xcart_variants.productid = xcart_products.productid AND
xcart_quick_prices.variantid = xcart_variants.variantid INNER JOIN
xcart_products_categories ON xcart_products_categories.productid =
xcart_products.productid INNER JOIN xcart_categories ON
xcart_products_categories.categoryid = xcart_categories.categoryid LEFT JOIN
xcart_category_memberships ON xcart_category_memberships.categoryid =
xcart_categories.categoryid WHERE xcart_extra_field_values.value >=
1288579556 and xcart_extra_field_values.value <=1289184356 and
xcart_quick_prices.priceid = xcart_pricing.priceid AND xcart_products.product_type
<> 'C' AND xcart_products.forsale <> 'B' AND
xcart_category_memberships.membershipid IS NULL AND
xcart_product_memberships.membershipid IS NULL AND (xcart_categories.avail
= 'Y' OR xcart_categories.avail = 'H') AND xcart_products_categories.categoryid
IN (
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,3
0,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,5
6,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,8
2,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,
106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,12
4,125,126,127,128,129,130,131,132,133,336,338,339,461,489,542,548,549,550,
551,552,553,554,555,556,557,558,559,560,561,562,563,566,567,574,575,576,57
7,578,579,580,581,582,583,584,585,590,591,593,594,595,597,598,599,600,608,
612,615,616,617,621,622,623,624,629,632,633,634,635,636,637,638,639,640,64
1,642,648,651,652,656,657,658,659,660,665,666,667,668,670,671,672,673,681,
685,686,687,695,696,698,699,703,704,705,712,721,722,725,733,734,735,736,73
7,738,758,760,767,768,769,770,772,779,780,784,788,789,791,792,794,795,797,
798,799,801,815,816,817,818,819,820,821,822,823,824,825,830,832,833,835)
AND (xcart_products_categories.main='Y' OR xcart_products_categories.main!='Y')
AND (xcart_products.forsale = 'Y' OR xcart_products.forsale = '') GROUP BY
xcart_products.productid ORDER BY ABS(EF_SORT.value) DESC,
xcart_products.product ASC, xcart_products.productcode ASC,
xcart_products.productid ASC;
If there is any info you need from these tables please just let me know. Thanks in advance for your time and knowledge on this.
