1. For this theory to work, we would need to add some more columns to the DescendentCountsMap table:
Current structure of g2_DescendentCountsMap:
g_userId g_itemId g_descendentCount
g_userId g_itemId g_descendentCount g_countStatus g_childrenInNeed/g_itemLevel
It is the status of the descendentCount in the row:
0: actual 1: updateNeededAtLowerLevel 2: updateNeededHere
0: default 1: when we invalidate _a child_ at any lower level (we will get this itemId from the parentSequence) 2: when doin invalidating
? g_childrenInNeed ?
(this part is skipable..as the next seems to be a 'more likely to work' one ) itemIds of the childrenItems which has countStatus 2 The childrenId would be written into entries of _all_ upper level(any parent) Ids. This way we wouldn't have to do 'deepscanning' by the parentSequence when we need the descendentCount for a parentId, and the childrenIds simply can be filled in, in the invalidatingcall. But on the other hand it could result in an itemId-sets with 100s ItemIds for a long ago logged in user..so could be a really good solution only with automatic groupping changes.. that way it should rare to have an enty with lots of itemids before it gets refreshed (as the users who cause the invalidating, might visit the page too which got invalated..and with a good chance at least 1 of them is in the same group as the 'long ago logged in' user.) ('this part' end)
? g_itemLevel ?
We could get the list of all children for an itemId, check for the ones with countStatus 2, and then do recounting of the uncached(invalited) ones somehow like this(pseudocode): get the itemIds which we need to do the recounting for:
SELECT itemId FROM g2_DescendentCountsMap,g2_ItemAttributesMap WHERE g2_ItemAttributesMap.parentSequence like items_table.userIsHereId AND g2_ItemAttributesMap.id=g2_DescendentCountsMap.id AND g2_DescendentCountsMap.g_countStatus=2 ORDER BY g2_DescendentCountsMap.g_itemLevel DESC;
(+ we need to check for the actual user..or -when groupping permission 'project' is done- group..) -in desc order, so we can start doing the recounting from the deepest level.. (note: we should have entries for all itemIds in g2_DescendentCountsMap and/or we need a fallback to generate it if not found)
This will give us the itemIDs of albums on which we REALLY need to run the recalculating function, for the current user/group. This could probably result in a long list, which this way should be passed between calls, or this could be a subquery (but that is supported mostly only in mysql 4.2/3+), but this list should be relativly short if we could group userperms.. (same 'need' here again..it's not a must for this to work, but would boost is up a lot, as visits of other users -within the same group as the current user _for some itemIDs_- would eliminate the most part of the recounting needs. Once the recountingprocess is done at Max(level), we can easily switch to MAX(level)-1, and recount the children of parents at this level (no childcounting at any deeper level now), and then we would simply finish this level with (Max(level)-1).directChildrenOfThisItem+sum[Max(level)] , where sum[Max(level)] could be simply read from g2_DescendentCountsMap, as all those will be cached, generally: by the time we reach LevelN-1, all the g_descendentCount of items at LevelN will be cached.
NOTE: updateNeededHere value 1 could be skipped, as we do a search for children with value 2..but on the other hand setting 1 to parents when a child changed could be faster (cheaper) to do, than to do this search (for nothing/in unneeded cases) for children with 2. So when that's simply 0, we wouldnt search any longer/deeper (or even wouldn start to search).
"GROUPING" note to self: SELECT iam0.g_itemId, COUNT( iam1.g_itemId ) FROM g2_ItemAttributesMap AS iam0, g2_ItemAttributesMap AS iam1, g2_AccessSubscriberMap WHERE iam0.g_itemId IN ( 304430 ) AND iam1.g_parentSequence LIKE CONCAT( iam0.g_parentSequence, iam0.g_itemId, '/%' ) AND iam1.g_itemId = g2_AccessSubscriberMap.g_itemId AND g2_AccessSubscriberMap.g_accessListId IN ( 288491 ) GROUP BY iam0.g_itemId LIMIT 0 , 30
FAST! => sep group/user acls, cache group, do user=> do 1/2/fix/consts acls qs! rockya! lehet kozos is.. szal v phpben vagy mysqlben subquerivel szurni.. mivel egyik keselve van=> phpben..de ott meg nincs az albumid keselve=>fck