Slow Weird Queries ! Anyone knows what is this?

[prestamoi]

This query's just happen and are slowing everything someone knows about this issue?

See the img attached!

thanks

Query time(258)

Sending data

SELECT DISTINCT cp.`id_product`
FROM `ps2_category_group` cg
INNER JOIN `ps2_category_product` c

[rocky]

I found this query on line 191 of classes/Search.php that looks like that query:

$eligibleProducts = $db->ExecuteS('
SELECT DISTINCT cp.`id_product`
FROM `'._DB_PREFIX_.'category_group` cg
INNER JOIN `'._DB_PREFIX_.'category_product` cp ON cp.`id_category` = cg.`id_category`
INNER JOIN `'._DB_PREFIX_.'category` c ON cp.`id_category` = c.`id_category`
INNER JOIN `'._DB_PREFIX_.'product` p ON cp.`id_product` = p.`id_product`
WHERE c.`active` = 1 AND p.`active` = 1 
AND cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (
   SELECT id_group FROM '._DB_PREFIX_.'customer_group
   WHERE id_customer = '.intval($cookie->id_customer).'
)').'
AND '.implode(' AND ', $whereArray));

The query is searching through all your products. You must have a lot of products if it is going slow.

[prestamoi]

I see this query is using category groups which in fact we don't use, you know a way to make it faster? we have around 10.000 items + and 500 subcategories

[rocky]

You could try taking out the customer group part:

AND cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (    SELECT id_group FROM '._DB_PREFIX_.'customer_group    WHERE id_customer = '.intval($cookie->id_customer).')').'