CODEWITHSUNDEEP
mysql
Jankeyfu
Jankeyfu

Reputation: 29

mysql query with LIKE may use index, but only use some len characters?

I have an SQL statement

select * from tb_name where field like 'abc_def_ghj%';

which can be as fast as

select * from tb_name where field like 'abc_def_ghj_defg%;

(there may be lots of field value starting with abc_def_qhj;

from the 12th letter are quite different).

I want to know if is there a special length for the index field, for example, only field(0,10) for the First ten letters effects;

Upvotes: 0

Views: 176

Answers (2)

Rick James
Rick James

Reputation: 142528

(I assume you have INDEX(field)?)

If only a small percentage of rows match this, then it is fast:

like 'abc_def_ghj_defg%

If a large percentage of rows match this, then it is slow:

like 'abc_def_ghj%'

The speed difference has very little to do with lengths, just with number of rows that match.

If the collation is ..._ci, then upper and lower case match each other.

Be aware that underscore (_) is a wildcard.

When timing, be sure to run a query twice. Caching could make a big difference.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37487

Yes, there a limits on the length of index keys in MySQL.

From "15.22 InnoDB Limits" (assuming you use InnoDB as storage engine because it's the most popular these days):

  • The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

    The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

    Attempting to use an index key prefix length that exceeds the limit returns an error.

    If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

    The limits that apply to index key prefixes also apply to full-column index keys.

Upvotes: 0

Related Questions