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,
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 ..

Options: ReplyQuote


Subject
Views
Written By
Posted
calculating total products in each category
3527
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.