MySQL 8 Administrator???s Guide

Index prefixes

This option allows the user to specify the number of characters for indexing in the case of a string. MySQL 8 has provided the option column_name(N), in index creation to specify a number of characters. Indexing prefers only specified characters, which will make the index file smaller. So, at the time of the BLOB and TEXT column, you must specify the prefix length for better performance. Consider the following example to create indexes with prefix lengths on the BLOB type:

CREATE TABLE person (personal_data TEXT, INDEX(personal_data (8)));

This command creates an index on the personal_data column by considering the first eight characters. The prefix length varies, based on the storage engine. The InnoDB storage engine allows ...