In MySQL will UNIQUE index act as fast lookup for varchar column?

Question

If I have a column short_title in MySQL table and it is defined as UNIQUE, do I also have to add FULLTEXT for it to be searchable really fast? Or does UNIQUE already guarantee it will be searchable quickly (without full table scan)?

Thanks, Boda Cydo

Answer 1

UNIQUE will locate the verbatim short_title using the underlying index.

If you need a word match (as opposed to verbatim match), use FULLTEXT index.

Also note that by default the B-Tree indexes in MyISAM against the VARCHAR columns are subject to key compression. This can slow down the searches for the titles closer to the end of the alphabet:

• Index search time depends on the value being searched

Finally, the VARCHAR keys tend to be large in size.

For the fastest verbatim searches, you should store a MD5 hash of the title in a BINARY(16) column, create a UNIQUE index over it (disabling the key compression) and search for the hash.