Simetrical wrote:
We don't have to move off MySQL, we just have to use a different system for this one feature. That's perfectly plausible; we use Lucene for search.
Ah, something I actually know something about. This is the third or fourth time, to my knowledge, that we've discussed category intersection in depth. Last year (I think it was last year) I did a bunch of pretty extensive testing, including running MySQL queries against the categories table using various methods (joins, subselects, you name it) and the consensus was that was way too slow (queries against large categories were awful - Living People was a test case).
So, I also loaded the categories into the cur table (I'm using an old schema) and created a field holding all the categories with underscores for spaces in the categories (like it appears in the url). This made MySQL's fulltext index see the whole category as one word. This performed *much* faster, and you could use boolean queries to get fancy.
I also created a lucene index which I queried with zend_search_lucene. This actually performed pretty comparably to the MySQL fulltext index. It's all in the archives somewhere. I think either of those solutions would probably be okay, but if it's wildly poplular the load might be a bit much. I didn't get (that I recall) any really conclusive opinions from the group or the core developers.
But, based on all that, here's my suggestion: create a new lucene index of categories using all the existing tools, and do boolean queries against that. I think it's the path of least resistance, and the performance should be quite acceptable (pretty much be definition).
On a related topic, anybody on the list mess around with clucene? I'm still playing with it off and on... (I'm a novice at c/c++) seems like a good choice for a high performance web based search (doesn't have the overhead of being Java)...
Best Regards, Aerik