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.

Answer 1

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.