I ran into a similar problem, and solved it by just moving the set processing off into PHP. Maybe not elegant, but it seems robust and fast under load.
I needed a system to apply filters based on an arbitrary number of selections from a couple different tables (people and keywords being stored differently), plus quickly show how many options remained under all the remaining possibilities.
e.g.: list everything that passes through the filter of 'keyword A, keyword B, and person X', while presenting further navigation options for keywords C-K and people Y and Z showing how many things would appear after adding each of them to the filter.
I queried for the base set of all relevant records and dumped their ids into an array, then queried for all record ids associated with keyword A. One array_intersect() later, I've got a new base set. I then do the same thing with my new base set and keyword B, etc. Once I've reduced to the current set of IDs, I can cycle through and intersect that with the full set from each of the remaining unused keywords to quickly find out how many options each will present.
It sounds like a lot of database hits, but they're very consistent. So, the first set of cache misses is expensive but once you've got them all loaded into cache it flies (assuming you've got caching turned on in MySQL). I've got a couple hundred keywords and people applied about a dozen each to a few hundred items, and my load average is pleasingly low. I suspect that if the number of items in each set that it's intersecting went up I'd need to start another layer of caching for top-level filter group results.
Hope this helps!
MySQL Intersect
Moderator: General Moderators