Gregory Maxwell wrote:
So here is the straight dope:
- Using joins for this will never be blindingly fast in the worst case.
- MySQL joins are slower than they could be because MySQL doesn't
support in memory bitmaps yet (although thats coming). 3) Other databases offer alternative indexing schemes which are fast in all cases.
So long as we are staying on MySQL (which is a good for many reasons, just not at all good for things which are more complex than simple web traffic), the only way for us to get acceptable results for this is going to be to call an outside piece of software exactly as we've done with Lucine for full text search.
It might be wise of us to instead consider figuring out how to integrate a link to PostgreSQL instead, which not only can do inverted index based full text search like Lucine, but can support our need for things like intersections, and future applications like fast geospatial queries. (Yes, mysql can do some of these things a bit better if you give up reliability and use MyISAM table type rather than innodb)...
Now, doing _that_ seems like a good idea, since it
a) will solve just one problem, and, it seems, solve it rather well b) will not affect critical functions if it fails: only category intersection queries should be affected by a failure c) leaves the MySQL database as still the master data repository d) will give the developers some experience in using PostgreSQL, which might come in useful in the future...
-- Neil