MySQL index for long strings

Question

I have MySQL InnoDb table where I want to store long (limit is 20k symbols) strings. Is there any way to create index for this field?

Answer 1

you can put an MD5 of the field into another field and index that. then when u do a search, u match versus the full field that is not indexed and the md5 field that is indexed.

SELECT *
FROM large_field = "hello world hello world ..."
AND  large_field_md5 = md5("hello world hello world ...")

large_field_md5 is index and so we go directly to the record that matches. Once in a blue moon it might need to test 2 records if there is a duplicate md5.

Answer 2

You will need to limit the length of the index, otherwise you are likely to get error 1071 ("Specified key was too long"). The MySQL manual entry on CREATE INDEX describes this:

Indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length:

• Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY columns.

• BLOB and TEXT columns also can be indexed, but a prefix length must be given.

• Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. That is, index entries consist of the first length characters of each column value for CHAR, VARCHAR, and TEXT columns, and the first length bytes of each column value for BINARY, VARBINARY, and BLOB columns.

It also adds this:

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.