Re: Where and Case sensitivity

[Rick James]

I'm stuck here:

> millions of rows in the initial Extracted table (ExtractedWordsToMatch).
> However these will be inserted ~1-2,000 per time.

The table name (ExtractedWordsToMatch) implies that you have not yet matched them against the OfficialWordList words.
It seems like it would be better to test the 1K-2K words first, then throw the matched (or maybe the unmatched?) words into a table called
ExtractedWordsThatMatch (or ExtractedUnmatchedWords)?

A LEFT JOIN is how to see what is in one table but not another.

If I had a million-row table with lots of dups, I would be tempted to do
SELECT DISTINCT ...
first to get rid of the dups.

> Would the JOIN work faster still without the distinct?

Try it both ways. It is good exercise in SQL to formulate the queries, run them, time them, and compare the outputs.

> I don't want the matching to be case sensitive, so I'll use utf8_unicode_ci.

Good; that issue seems to be settled.