MySQL Forums :: Optimizer & Parser :: calculating total products in each category


Advanced Search

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 3492 tep . 12/16/2009 04:26AM
Re: calculating total products in each category 1733 tep . 12/17/2009 09:58AM
Re: calculating total products in each category 1783 Paul Nichitoiu 12/18/2009 07:46AM
Re: calculating total products in each category 1778 tep . 12/18/2009 07:50AM
Re: calculating total products in each category 1683 tep . 12/23/2009 08:15AM
Re: calculating total products in each category 1674 Rick James 12/29/2009 09:54PM
Re: calculating total products in each category 1732 tep . 12/30/2009 02:35AM
Re: calculating total products in each category 1758 Rick James 12/30/2009 08:09PM
Re: calculating total products in each category 2021 tep . 12/31/2009 12:39AM
Re: calculating total products in each category 2739 tep . 12/31/2009 01:42AM
Re: calculating total products in each category 2896 tep . 01/04/2010 08:13AM


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.