Re: Where and Case sensitivity
Posted by:
Joey JJ
Date: August 06, 2014 02:12AM
Thanks again for the reply Rick,
Sorry, I've confused things a little,
So my plan is to keep a 'Master table', ExtractedWordsToMatch, that will audit all words that were ever attempted to be matched.
This table will be purged, but, in order to learn the 'junk words', i.e. pronouns, etc (but other perhaps real, but non-applicable-to-the-task words) so that they can be pre-filtered, either by a less-hungry proc, or by php, etc.
So after ~1,000 people have had text extracted, there'll be millions of rows to look for junk words - in reality, perhaps I'll purge every few hundred users.
My concern was that as ExtractedWordsToMatch grew, the inserts would become less efficient. Therefore I thought it safe to not index the column word in ExtractedWordsToMatch, but instead on the child temp table.
And thanks, I've been load testing this. I thought my WHERE EXISTS statement was executing faster than JOIN, but I didn't consider that the 'hard work' had already been done by the DISTINCT.
I'll test with and without the temp table, but am I correct to think that, at first glance, you would consider my temp table approach redundant?
Thanks again
Joey