I put the fulltext stuff in a separate table to give us options:
If we used an external fulltext thing like elasticsearch, how easy would it be to strip fulltext searching out of the database?
If we required time as a parameter to fulltext searches, could I have a path to increasing performance of time-bounded queries (partition by time)
But I think it's been super useful anyway. The text on this site is largely static, so I'm able to keep the vacuuming separate, and the backups are easier to push across the wire because that stuff hasn't changed, etc.
The search_index table we have holds all fulltext... from sites, forums, threads, comments, PMs... everything. Which is how searching across multiple types of things is so easy. Each type of thing has it's own trigger to push a copy into the full text index table.
I put the fulltext stuff in a separate table to give us options:
But I think it's been super useful anyway. The text on this site is largely static, so I'm able to keep the vacuuming separate, and the backups are easier to push across the wire because that stuff hasn't changed, etc.
The search_index table we have holds all fulltext... from sites, forums, threads, comments, PMs... everything. Which is how searching across multiple types of things is so easy. Each type of thing has it's own trigger to push a copy into the full text index table.