Author: Yao Yuan

MySQL ACE, Huawei Cloud MVP, focused on Oracle and MySQL database for many years, Oracle 10G and 12C OCM, MySQL 5.6, 5.7, 8.0 OCP. Now Dingjia Technology is a technical consultant, providing database training and technical support services to colleagues and customers.

Source of this article: original contribution

* Produced by the open source community of ecoson, the original content is not allowed to be used without authorization, please contact the small edition and indicate the source.

When creating an index on a field of a very long string, the index becomes very large and inefficient. One solution is for the CRC32 or MD5 functions to hash a long string and then create an index on the result of the calculation. In MySQL 5.7 and later, it is possible to create an automatically generated field. For example, you can create the following table:

create table website(
id int unsigned not null,
web varchar(100) not null,
webcrc int unsigned generated always as (crc32(web)) not null,
primary key (id)
);

Insert a record into this table:

mysql> insert into website(id,web) values(1,"https://www.scutech.com"); Query OK, 1 row affected (0.07sec) MySQL > select * from website; +----+-------------------------+-----------+ | id | web | webcrc | +----+-------------------------+-----------+ | 1 | https://www.scutech.com | | + 851176738 - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)

You can see that the cyclic redundancy check value of the Web field is automatically generated in the field WebCRC. Creating an index on this field results in a space-efficient index that takes up less space.

In MySQL 8.0.13 and later, it is possible to create a functional index directly. For example:

create table website8(
id int unsigned not null,
web varchar(100) not null,
primary key (id),
index ((crc32(web)))
);

SELECT * FROM INDEX ON TABLE * FROM INDEX ON TABLE

mysql> show index from website8\G *************************** 1. row *************************** Table: website8 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: website8 Non_unique: 1 Key_name: functional_index Seq_in_index: 1 Column_name: NULL Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: INDEX_TYPE: Btree COMMENT: INDEX_COMMENT: Visible: YES EXPRESSION: CRC32 (' WEB ') 2 rows in set (0.00 SEC)

You can see that the first index is a primary key and the second index is a functional index.

Another way to fix the length of an index is to create a prefix index. The syntax for creating a prefix index is: col_name(length). The prefix index creates an index on the first part of a string. CHAR, VARCHAR, BINARY, and VARBINARY. The key to creating a prefix index is to select the length of the prefix string. The longer the length, the more selective the index is, but the more storage space is available.

SQL > select * from table sbtest2 where c = 120-length string;

mysql> select count(distinct(left(c,3)))/count(*) sel3, count(distinct(left(c,7)))/count(*) sel7, count(distinct(left(c,9)))/count(*) sel9, count(distinct c)/count(*) selectivity from sbtest1; +--------+--------+--------+-------------+ | sel3 | sel7 | sel9 | selectivity | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | | | 1.0000 1.0000 0.9959 0.0120 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (1.66 SEC)

You can see that the selectivity of the index can be achieved by creating an index in the first 9 bits of the field. If you increase the prefix number of the index, the selectivity of the index will not be improved.

mysql> alter table sbtest2 add index (c(9));