Hi Guys,
I have 4.5 million products, 44,000 manufacturers and 7,500 categories.
Here's the query:
SELECT
m.manuname,
m.idmanufacturers,
r.retailername,
r.idretailers,
i.iditems AS id,
i.image,
IF(COUNT(iddeals)>0, 'item', 'deal') AS `type`,
nodes.idcategories,
nodes.categoryname,
ucwords(i.name) AS `text`,
i.description AS description,
i.complete,
IFNULL(MIN(d.price), '0') AS price,
IFNULL(MAX(d.price), '0') AS priceto,
IFNULL(AVG(rv.stars), '0') AS rating,
COUNT(d.iddeals) AS deals
FROM
items AS i
INNER JOIN
categories_items_idx AS idx
ON (idx.iditems = i.iditems)
INNER JOIN
categories AS nodes
ON (nodes.idcategories = idx.idcategories)
INNER JOIN
categories AS c
ON (nodes.lft BETWEEN c.lft AND c.rgt)
INNER JOIN
deals AS d
ON (i.iditems = d.iditems)
LEFT OUTER JOIN
manufacturers AS m
ON (i.idmanufacturers = m.idmanufacturers)
LEFT OUTER JOIN reviews AS rv
ON (rv.iditems = i.iditems)
LEFT OUTER JOIN retailers AS r
ON (d.idretailers = r.idretailers)
WHERE
i.active = '1' AND `r`.`active` = '1'
AND
d.idlanguages = '1' AND d.idcurrencies = '1'
AND
c.idcategories = '1075'
GROUP BY
i.iditems
ORDER BY
deals DESC
LIMIT 10
Okay - so I have created indexes on every joined and searched column in all tables and i'm still not really getting anywhere.
They are all INNODB tables.
So my question is what can I update on the server side (my.cnf) to optimize for this very large database and complicated queries?
Any ideas anyone?