MySQL Forums
Forum List  »  Optimizer & Parser

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,
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.ctgid = product.category
AND = '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`)


Edited 1 time(s). Last edit at 12/16/2009 05:49AM by tep ..

Options: ReplyQuote

Written By
calculating total products in each category
December 16, 2009 04:26AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.