MySQL Forums
Forum List  »  Newbie

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

Options: ReplyQuote


Subject
Written By
Posted
August 02, 2014 02:33AM
August 02, 2014 01:11PM
Re: Where and Case sensitivity
August 04, 2014 12:31AM
August 04, 2014 01:17PM
August 04, 2014 06:55PM
August 05, 2014 12:21AM
August 06, 2014 02:12AM
August 06, 2014 06:50PM
August 07, 2014 05:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.