MySQL index for long strings Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of MySQL index for long strings without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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

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.

We are here to answer your question about MySQL index for long strings - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji