Possible INDEX on a VARCHAR field in MySql

I am working in a MySql database, with a table like this:

+--------------+
|  table_name  |
+--------------+
|    myField   |
+--------------+

...and I need to make a lot of queries like this (with 5-10 strings in the list):

SELECT myField FROM table_name
WHERE myField IN ('something', 'other stuff', 'some other a bit longer'...)

There will be around 24.000.000 unique rows

1) Should I use a FULLTEXT or and INDEX key for my VARCHAR(150)?

2) If I increase the chars from 150 to 220 or 250... would it make a great difference? (Is there any way to calculate it?)

3) As I said, they are going to be unique, so myField should be a PRIMARY KEY. Isn't it rare to add a PRIMARY KEY to a field which is already a VARCHAR INDEX/FULLTEXT?

SUGGESTION #1 : Standard Indexing

CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    key (myfield)
);

If you index like this, you can either look for the whole string or do left-oriented LIKE searches
SUGGESTION #2 : FULLTEXT Indexing

CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    fulltext (myfield)
);

You can effectively use searches for individual keywords as well as whole phrases. You will need to define a custom stopword list because MySQL will not index 543 words.

Here are my other posts from the past two years on FULLTEXT indexes

• May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)

• Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional

• Jan 26, 2012 : Mysql fulltext search my.cnf optimization

• May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

SUGGESTION #3 : Hash Indexing

CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    hashmyfield char(32) not null,
    primary key (id),
    key (hashmyfield)
);

If you are looking for one specific value and those values could be lengths well beyond 32 characters, you could store the hash value:

INSERT INTO mytable (myfield,hashmyfield)
VALUES ('whatever',MD5('whatever'));

That way, you just search for hash values to retrieve results

SELECT * FROM mytable WHERE hashmyfield = MD5('whatever');

Give it a Try !!!

StackExchange Database Administrators Q#35821, answer score: 91