Timwi,
I'm not sure where you get this idea that each intersection would be a separate table. Obviously it wouldn't, it would still be one single database table, containing everything. And this single table can be queried for a single category, which (assuming proper indexes are in place) would not be any less efficient than it already is currently.
Sure, now there's another issue - what happens (well, it already happens with Living People), when people use categories like tags. Simply, amount of big categories increases.
When you do an intersection of a small and big categories, you end up with nested loop starting on a small one, checking for entry existence in big one. Then you sort the small resultset. When you do an intersection of two big categories, you end up with nested loop going over two big tables, and a big resultset finally, with big resultset to sort.
Right now we try to avoid sorting for categories at all - we use index-based sorts.
B+Trees are not that good for intersections, now maybe fulltext indexing in general is more suitable for such task, that would mean us using external facility for lookups like that (well, that was discussed in frankfurt 2005 ;-), but it is far more complex than to throw few more queries at our core databases and expecting a miracle.
BR,