Hi everybody. I'd love to get some feedbback on my proposal to create a separate MyISAM table for fulltext searches of categories, to enable easy, efficient category intersection searches. Based on the numbers I throw out yesterday, the number of writes to the table shouldn't cause a problem. Based on testing I did, the query performance is quite good. I'm going to mangle a copy of the categorylinks table to create a fulltext index on millions of small records, and I'll test it further, but I feel quite positive right now.

What I think woud have to happen, is that we'd compare the new version of an aritcle to the old one to see if the categories have changed. I'm pretty sure the a comparison is already being done somewhere between the old and new versions, so this would be minimal additional overhead added at that point. If the categories are changed, then we'd write the new categories to the new table in an update operation.

If we're really terrified of a MyISAM table crashing (I'm not, but you guys are the pros), we could keep a copy the same data (the categories) in the text table as well, to be able to easily recreated teh search table.

What do you think?

Thanks, Aerik