Optimization of MYSQL query?

[FlightToNothing]

Hello,
I use a prestashop 1609 and I have around 10,000 products.
I received a message from the hosting company with a recommendation to optimize a mysql query on my site (sql log is in the attachment)
This query consumes a lot of processor time.

In prestashop, this request is located in Product.php at line 2177.

$sql = 'SELECT product_shop.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute
					FROM `'._DB_PREFIX_.'product` p
					'.Shop::addSqlAssociation('product', 'p').'
					LEFT JOIN  `'._DB_PREFIX_.'product_attribute` pa ON (product_shop.id_product = pa.id_product)
					'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1').'
					WHERE product_shop.`active` = 1
						'.(($ids_product) ? $ids_product : '').'
						AND p.`id_product` IN (
							SELECT cp.`id_product`
							FROM `'._DB_PREFIX_.'category_group` cg
							LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
							WHERE cg.`id_group` '.$sql_groups.'
						)
					'.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').'
					GROUP BY product_shop.id_product
					ORDER BY RAND()';

Can you recommend a way to optimize?

Thanks!

 

sqllog.txt