Re: calculating total products in each category

[tep .]

I finalized the situation and divided small part instead of big whole part :-)

Here is the solution that anyone who needs face with same problem !

1. truncate table tmp_category_total

2. insert into tmp_category_total
(category,total)
select category,count(*)
from product
where active = ?active
group by category

3. update category set total = 0

4. UPDATE category c,(SELECT parent.ctgid, sum(summary_product.total) adet
FROM category AS node,
category AS parent,
tmp_category_total summary_product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.ctgid = summary_product.category
GROUP BY parent.ctgid
ORDER BY node.lft) a
SET c.total = a.adet
WHERE c.ctgid = a.ctgid


Total duration about 1 second !

Thanks anyone who helped me especially to Rick James :)

tep



Edited 1 time(s). Last edit at 01/04/2010 08:14AM by tep ..