CODEWITHSUNDEEP
mysqlmd5
Andres SK
Andres SK

Reputation: 10974

What field structure would be better for a definition table in MySQL?

I'm making a dictionary webapp. The user will search for words. Would it be faster to do this?

SELECT * from definition WHERE word LIKE "house";

or...

SELECT * from definition WHERE word_hash LIKE md5("house");

In the second example, I store the md5() value of words in the word_hash field. Of course, "word" and "word_hash" are indexes.

Update: sometimes, the word field could be more than 1 word. Example: Sacré Bleu

Upvotes: 0

Views: 88

Answers (1)

mu is too short
mu is too short

Reputation: 434575

Skipping LIKE completely would be faster. Added the lower case version of word as word_lc, index word_lc, and then do:

select * from definition where word_lc = lower(word_you_want)

Using LIKE without any % or _ wildcards is just a case insensitive equality test so you should go straight to a case insensitive comparison that can and will take advantage of an index. Also, as usual, say what you mean so the computer can do what you want it to do.

Upvotes: 1

Related Questions