Category box Performance

This message was sent from: Suggestions and Proposals
http://forums.oscommerce.com/viewtopic.php?p=198743#198743
----------------------------------------------------------------

Hello,
I was working on some stuff and decided to log some more data about what queries are sent to the DB backend to see how much impact they have on performance.

I noticed that for a default install the cataegory box (Whenn browsing the hardware category) sends 69 queries to the DB on a total of 132. (This total will obviously vary depending on the data available, and options configured)

One of the reasons for this number of hits is that the DB gets queried twice for every displayed categorie:
 - once to fetch data like name, id and such
 - once to find out if there are any subcategories.

This can easily be avoided by rewriting the code to do this in one  query, like: 

[code]
SELECT 
	DISTINCT c.categories_id, cd.categories_name, c.parent_id, c.sort_order, count(c2.categories_id) AS sub_categories
FROM 
	categories_description cd, categories c
LEFT JOIN
	categories c2 ON c.categories_id = c2.parent_id
WHERE 
	c.parent_id = '0' AND 
	c.categories_id = cd.categories_id AND
	cd.language_id='1' 
GROUP BY 
	c.categories_id, cd.categories_name, c.parent_id, c.sort_order
ORDER BY
	sort_order, cd.categories_name
[/code]

An second reason is that when 'SHOW_COUNT' is true every category gets queried twice regardless of wether they get displayed:
 - once to find out if there are any subcategories.
 - once  to count the number of products

This could be done in one query, but I have no cut-n-paste solution ready ;-)

A solution would be to query every category regardless of being displayed or not exactly once (if 'SHOW_COUNT' is true) and pick-and-choose from the resultset what you need to generate the catagory list.

A catchall query would be:

[code]
SELECT 
	DISTINCT c.categories_id, cd.categories_name, c.parent_id, c.sort_order, count(p.products_id) AS product_count, count(c2.categories_id) AS sub_categories
FROM 
	categories_description cd, categories c
LEFT JOIN 
	products_to_categories p2c ON c.categories_id = p2c.categories_id
LEFT JOIN 
	products p ON p2c.products_id = p.products_id
LEFT JOIN
	categories c2 ON c.categories_id = c2.parent_id
WHERE 
	c.parent_id = '0' AND 
	c.categories_id = cd.categories_id AND
	cd.language_id='1' 
GROUP BY 
	c.categories_id, cd.categories_name, c.parent_id, c.sort_order
ORDER BY
	sort_order, cd.categories_name
[/code]

So these proposed changes would result in:
- One DB hit per displayed category when 'SHOW_COUNT' is false
- One DB hit per available category when 'SHOW_COUNT' is true

I think these changes could improve performance quite a bit, especially for busy shops with lot's of categories.

I don't have a complete fix in hand, but I'll be more than happy to submit patches with the proposed changes, if there's a chance of them being merged into osC. 

Opinions?