It also occurs to me that if this work is going to be done, each category could also store the number of items, which would relieve the necessity to do a SELECT COUNT(*) query for each one, and perhaps also in the process fix the mysterious subcategory count bug that has been reported by me and at least one other person. ;)
I feel strongly these are optimizations that would be very good for K2 performance since the ratio of read to write is typically going to be very high. Certainly caching is advisable, and "Catalog Mode -> Yes" is an acceptable switch for some people, but this would be far superior to that.
The only downside I can see to these suggestions is that there would need to be an integrity checker that goes through all the categories and items and validates the denormalized columns in the case that mysql has a problem, although that would not be a difficult utility to write at all.
So, in conclusion, storing a childcount and itemcount column in the categories would be all that's needed, and would provide great optimizations.
-No need to do count(*) for items
-No need to query for items when itemcount == 0
-No need to iterate on a subcategory if childcount == 0.
This could be *supercharged* with the addition of a category_item_xref table which would store a full list of all child categories for a category. It would simply flatten out the hierarchy so that you have a 1-M of all the child, subchild, subchild etc. categories for a particular category. Needless to say there would be no need to ever recurse given the availability of this table and associated index.
I haven't fully thought it through, but the itemcount in the category would eliminate concerns about displaying itemcount data, as you would be able to get everything required in one query/result set to display category and item data.
David R. said:This could easily be optimized with the addition of a denormalized "hasChild" column to the k2_categories table. When a child is added or deleted, this flag would need to be updated. For situations like the ones described, it could be used to short circuit unnecessary queries for categories with no children. By the same token it makes sense to also have a "hasItem" flag.