MySQL: Prefix Index

It’s not a new topic, but still have the issue for many developers.

What’s prefix index?

Normally, when you create index for string columns, index will use whole length of that fields. If your string field length is small, which is okay, but in most case the string fields are defined like 255 in size, but only use 30 or 50 of them. In this case, we can use prefix index to save a lot of disk space on index file, and also speed up insert and search operations.

For example, you can create an index using the first 12 characters of the FullName column like:

CREATE INDEX part_fullname ON users (fullname(12));

What the developer should know?

As we mentioned, the prefix will help to improve the performance on string fields, but there are some restricts to use prefix index:

• Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY column indexes.
• Prefixes must be specified for BLOB and TEXT column indexes.
• Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix limit is 1000 bytes. The NDB storage engine does not support prefixes
• Prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements is interpreted as number of characters for nonbinary string types (CHAR,VARCHAR, TEXT) and number of bytes for binary string types (BINARY, VARBINARY, BLOB). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.
• For spatial columns, prefix values cannot be given.
• If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix, cannot contain NULL.In another words: Indexed columns must be NOT NULL.

Like above example, why we define prefix index length is 12, because it’s measured by bytes, and full name field normally is utf8 type, so one character will be 3 bytes, if you defined not 3 multiples, then you will get some weird results during the search full name.

For text field, if you want to search a certain string in the text field, it’s better to create fulltext index, not prefix index. But if you want to sorting this text field, you can create prefix index.