I need to store several millions of string with a length from 20 up to 700 characters. The sources is UTF8, so I need to create an UTF8 column to store it.
I know MySQL has limitation in the number of bytes it can index and this is also affected by the character set, so I get errors when I try to create an index on this column.
As I'm almost sure I'm not the first trying to do this, I guess there might be some known tricks to workaround this. One I came up with is to use a hash function and use this for the key, the problem is that no hash function will guarantee a unique key.
Any suggestion is very welcome.

Try FULLTEXT index:
This can handle your large data request.

mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );

mysql> SELECT id, MATCH (title,body)
-> AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
-> FROM articles;

Yes, the FULL TEXT index is the best option to index text based columns and do text based searches, although you do have to take care of the following things:

1. Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

2. MySQL doesn't index small words (length 3 or less) by default.

Brijneet Bhasin
DB Team @ Clarion
Clarion Technologies
(SEI CMMI Level 3)
Pune, India
www.clariontechnologies.co.in
www.vEmployee.com