PERFORMANCE! Central caching for category API functions

Issue Summary

I'm opening this as 5.x-1.1 issue, because that's the version where I had to fix this quickly, and so it's where the initial patch applies. But however, I'm going to rework this for 6.x ASAP, as it's a stopper for my site's upgrade.

The problem and example of my site: It's known for a while that Category is a quite resource-intensive module. It didn't bother me that much until yesterday, when the hosting provider took my site down because it was overloading the shared server too badly. There were many slow queries, most of them originating from Category module (install with some sub-modules). To outline the performance profile of my site - from initial state, I saved as much as 25% of total pages generation time with this patch, and that's a lot for single module out of many. Another 25 to 30% was saved by disabling Statistics module, disabling certain blocks, adding indexes and removing unnecessary DISTINCT from queries, across core and contrib present on the site - in other words, all the other optimizations together are more or less equal to this Category patch alone. The remaining <50% of initial time includes core, quite a few contribs, and several heavy Views-made sidebar blocks, so we're not exactly speaking about 25% out of nothing.

The patch: What this patch does, is adding a central caching mechanism to the query-heavy data loading functions, mostly found in category.inc. There are already attempts on caching visible in code, somewhere static variables, somewhere even a kind of central function handling the queries, but it doesn't cover everything, *and* it only just employs static cache, so it helps with repeated calls in the same request, but doesn't really help with these numerous slow queries on every single page. All the same, existing code made the database server choke - some of the queries seen in category.inc are really horrible, joined multiple times, forcing MySQL to use temporary tables, filesorts etc., and despite static caching, they still need to run on every page (in some cases).

The basic idea of this patch is, that we cache all the data from "horrible" queries not only in static variable, but also in database, to make it persistent across requests. We cache it per-node, so that all data relevant to a given node may be loaded by single query from the cache (as it's quite likely, that a node being processed will need more of the data retrieved by various API functions later). And we make the mechanism central to all Category functions, by defining an uniform function call, minimizing impact on the code in each API function, and allowing for the desired bundled per-node storage of all data.

So first (and biggest) part of the patch is transformation of all the functions (that are likely to benefit from this) to use the new uniform function for caching, instead of previous static / old function / none workflows. It's just a simple get / rebuild+set-if-empty structure, uniform to all these.

Once we have all the caching in a central function (which is a single function with get/set/flush ops, to allow for easy static caching), we're almost there. First, the caching function have static variable caching, which is therefore applied to all the callers (i.e. more places than it was). Second, the cache is stored to a new database table {cache_category}, in the form of merged node-oriented records (serialized), so once a node is queried once, all API functions can process it without further queries (assuming the cache is already built), reducing the whole bunch of horrible joined queries into a single and simple cache get.

With node ID equal to zero, various global data (such as full tree and the like) are stored.

The database cache may persist quite long, not even flushed by most postings on the site. The positive thing about this approach is, that we've all cached data per-node, so we can only flush the cache for the very node being saved or deleted, preserving all the rest. This only applies to regular nodes, obviously - if a category or container gets saved, we need to flush the whole cache, as the hierarchy might be affected. But saving categories and containers is usually not very common. We also flush the zero-key global data on every node save (as it might be affected), but considering that viewing content is much, much more frequent than saving, this is still a win. The horrid big queries now only run once, after a node gets saved.

Since we have the caching centralized, the flushes are now handled directly as a part of hook_nodeapi operations, so the various $reset flags passed around might be obsolete now (although I didn't remove them in this patch).

This patch is a quick fix created overnight for my site, but it appears to work quite well, doesn't break anything, and saves a lot of time (plus saved my site from being shot by the hosting provider). The patch have code style issues at the moment (weird half-indentations, to keep the patch file small where actual code didn't change), but I really consider it a proof-of-concept at this point. I'm going to try, and implement the same for 6.x version, and then roll a proper patch with chances to be considered for commit.

Anyone wanting to try this 5.x-1.1 patch: It's not much tested, although it runs on my production site now, and helps a lot. It's against Category 5.x-1.1, but I didn't really try to apply it to a clean Category package (my code is hacked/patched a bit - I guess a bit of offset is the only trouble, but who knows). The database table needs to be created manually - there's no update function in the patch! I just asked in PhpMyAdmin "SHOW CREATE TABLE cache_filter", changed table name in the returned query to "cache_category", and executed that to create the new table.