calculating total products in each category
Posted by:
tep .
Date: December 16, 2009 04:26AM
hi all,
i am trying to find total active product for each category from root to leaf node.
need to really help to optimize sql, please helppp
slow query
---------------
SELECT parent.ctgid, COUNT(1) total
FROM category AS node ,
category AS parent,
product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.ctgid = product.category
AND product.active = 'Y'
GROUP BY parent.ctgid
ORDER BY node.lft
explain extended
-------------------------------------
1, 'SIMPLE', 'product', 'ref', 'ind_active1,ind_active_name', 'ind_active1', '6', 'const', 68, 'Using where; Using index; Using temporary; Using filesort'
1, 'SIMPLE', 'node', 'eq_ref', 'PRIMARY', 'PRIMARY', '2', 'product.category', 1, ''
1, 'SIMPLE', 'parent', 'ALL', '', '', '', '', 2976, 'Using where'
category indexes
-----------------------
PRIMARY KEY (`ctgid`),
KEY `ind_parent` USING BTREE (`parent`),
KEY `ind_topctg` (`topctg`),
FULLTEXT KEY `search_ind` (`catname`)
product indexes
----------------------
KEY `ind_active1` (`active`,`category`),
KEY `ind_uid` (`uid`),
KEY `ind_active_name` (`active`,`name`),
KEY `ind_promotion` USING BTREE (`promotion`,`companyuid`,`p_start`,`p_end`),
KEY `Index_8` (`companyuid`),
FULLTEXT KEY `search_name_keyword` (`name`,`keyword`)
thanks
tep
Edited 1 time(s). Last edit at 12/16/2009 05:49AM by tep ..