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