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?

    what datatype are the fields you are doing the joins on?

      Hi,

      what do you get if you execute the query

      EXPLAIN SELECT ....
      (without the limit)

      That will fetch optimizer information about the query execution plan (possible indexes, used indexes, temporary tables ...).

      How long does the above query run and how many rows does it return ?

      T.

        Write a Reply...