I do plan to eventually do that here, but prefer not to until the size of the public message search table requires it. I like having the whole thing searchable for as long as possible, plus it's a whole 'nother project to implement that. What I'm doing now is a first step in that project.
But another reason I plan to eventually do it that way is because on SI, the year the user selects dictates which message table will be searched. Only affects the "FROM" clause.
The way it's done here and will continue to be done until I make that change, the query also includes a somewhat expense set of date checks in the where clause.
Looks like the query to populate the publicmsgsearch table should be done in another several minutes. And it's still not really burdening the database box. Unfortunately, I'll definitely have to wait until afterhours to enable full-text indexing on it. I learned the hard way on SI that when you start a full index population, you can kiss about 80% of your CPU goodbye for hours.
I've written the beginnings of the query that'll keep the publicmsgsearch table synched but can't finish it until I've removed the fields from the search table that we won't need. Which is a LOT of them.
If you get the sneaking suspicion I seriously sandbagged the ETA on this, I (sneakily) suspect you might be right. I'm planning to work on it until my daughter calls to tell me to come home and bring or fix dinner. <g>
AI
