Ok I have a query that is shown below that LEFT JOINS xcart_extra_field_values. When I remove this LEFT JOIN the query is very fast. But when I include it man its slooooow. Can anyone please point me in the right direction to speed this baby up.
Your time and help on this is VERY much appreciate.
SELECT xcart_products., xcart_quick_flags., xcart_quick_prices.variantid, xcart_pricing.price, IF(xcart_products_lng.productid != '', xcart_products_lng.product, xcart_products.product) as product, IF(xcart_products_lng.productid != '', xcart_products_lng.descr, xcart_products.descr) as descr, IF(xcart_products_lng.productid != '', xcart_products_lng.fulldescr, xcart_products.fulldescr) as fulldescr, IFNULL(xcart_variants.avail, xcart_products.avail) as avail, IFNULL(xcart_variants.weight, xcart_products.weight) as weight, IFNULL(xcart_variants.productcode, xcart_products.productcode) as productcode FROM xcart_pricing, xcart_products LEFT JOIN xcart_quick_flags ON xcart_quick_flags.productid = xcart_products.productid LEFT JOIN xcart_products_lng ON xcart_products_lng.productid = xcart_products.productid AND xcart_products_lng.code = 'US' LEFT JOIN xcart_product_memberships ON xcart_product_memberships.productid = xcart_products.productid LEFT JOIN xcart_extra_field_values ON xcart_products.productid = xcart_extra_field_values.productid LEFT JOIN xcart_extra_fields ON xcart_extra_field_values.fieldid = xcart_extra_fields.fieldid AND xcart_extra_fields.active = 'Y' 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_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.forsale = 'Y' OR xcart_products.forsale = '') AND (xcart_extra_field_values.value LIKE '%Stoya%' AND xcart_extra_fields.fieldid = '6') GROUP BY xcart_products.productid ORDER BY xcart_products_categories.orderby ASC, xcart_products.product ASC, xcart_products.productcode ASC, xcart_products.productid ASC
And xcart_extra_field_values table info is attached as an image. If you need anything else, please just ask.
