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
- Prefixes must be specified for
- Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for
InnoDBtables or 3072 bytes if the
innodb_large_prefixoption is enabled. For
MyISAMtables, the prefix limit is 1000 bytes. The
NDBstorage engine does not support prefixes
- Prefix limits are measured in bytes, whereas the prefix length in
ALTER TABLE, and
CREATE INDEXstatements is interpreted as number of characters for nonbinary string types (
TEXT) and number of bytes for binary string types (
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
UNIQUEindex, the column values must be unique within the prefix, cannot contain NULL.In another words: Indexed columns must be
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.