Re: Where and Case sensitivity
Posted by:
Joey JJ
Date: August 04, 2014 12:31AM
Thank-you for the replies so far,
As requested, here is more SQL:
DROP TEMPORARY TABLE IF EXISTS t_CurrentKeywords;
CREATE TEMPORARY TABLE t_CurrentKeywords (
word varchar(255)
);
ALTER TABLE t_CurrentKeywords CONVERT TO CHARACTER SET utf8 collate utf8_unicode_ci;
INSERT INTO t_CurrentKeywords(word)
SELECT DISTINCT
word
FROM ExtractedWordsToMatch;
ALTER TABLE t_CurrentKeywords ADD INDEX t_CurrentKeywords(word);
SELECT w1.word
FROM OfficialWordList w1
WHERE EXISTS (
SELECT 1
FROM t_CurrentKeywords
WHERE t_CurrentKeywords.word = w1.word
);
So the List of User Inserted Words will be extracted and inserted, via PHP, into ExtractedWordsToMatch - they will be sorted in PHP before Insert, though there are no Indexes on Column word in the table ExtractedWordsToMatch.
From here, they will be inserted into a temporary table.
This is because I've read that it is much quicker to add Indexes after Inserts - and, ExtractedWordsToMatch will contain words from many users, whereas the temporary table will have a WHERE Statement so that it will consider the words of only one user.
So, the idea is that the words extracted in t_CurrentKeywords are matched against an official list of words contained in OfficialWordList - with OfficialWordList.word also Indexed.
However, the functionality I wish to build, is that the returned words are the official words - which are case sensitive.
Whereas I wish to allow my users to use whichever case sensitivity they require and still register as a match.
Barry's suggestion of having a second column SET 'UPPERWord' = UPPER(word) seems like a good idea?
This way, I insert UPPER(ExtractedWordsToMatch.word) into t_CurrentKeywords, then match on OfficialWordList.UPPERWord, but return OfficialWordList.word?
Thanks
Joey