FULLTEXT key too long

Question

I get the following error when trying to add a FULLTEXT index to Title, Edition and Author in my MySQL database:

Specified key was too long; max key length is 1000 bytes

Here are the columns:

`Title` varchar(255) NOT NULL,
`Edition` varchar(20) default NULL,
`Authors` varchar(255) default NULL,

None of them are unique.. Even the combination of all 3 is not unique. Primary key on the database is the ISBN.

The reason I'm adding this FULLTEXT index is so people can search for books using a keyword that might include the Title, the Edition, or the Author.

The answer to this question was already given [here](http://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-too-long-max-key-length-is-1000-bytes/3489331#3489331). — dma_k, Nov 10 '11 at 10:08

score 2 · Accepted Answer · answered Aug 09 '10 at 04:57

MySQL has a limit on how much space is allocated for indexes - it's 1000 bytes for MyISAM, 767 for InnoDB.

The only way around the limitation is to define keys that index a portion of the column - which means that's all that will be in the index, limiting it's value:

CREATE INDEX index_name ON YOUR_TABLE(title(100), edition, authors(100));

That will index the first 100 characters of the title column, all of the edition column, and 100 characters of the authors column. I believe is a 3 to 1 ratio for VARCHAR columns, so VARCHAR(250) takes 750 bytes.

FULLTEXT key too long

1 Answers1