MySQL redundancy and duplicate Indexes

Redundancy and duplicate index redundancy and duplicate index concept: MySQL allows you to create multiple indexes on the same column, whether there is ..

Redundancy and duplicate index redundancy and duplicate index concept: MySQL allows you to create multiple indexes on the same column, whether there is ..

Redundancy and duplicate Indexing

Concept of redundancy and repeated indexing:

MySQL allows you to create multiple indexes on the same column, either intentionally or unintentionally. MySQL needs to maintain duplicate indexes separately. The Hong Kong virtual host and the optimizer also needs to consider the query optimization individually. The Hong Kong virtual host will affect the performance.

Duplicate index: an index of the same type is created on the same column in the same order. Avoid creating duplicate indexes in this way and remove them immediately after discovery.

Eg: sometimes duplicate indexes are created inadvertently.

Create table test (id int not null primary key, a int not null, INDEX (ID) ENGINE = InnoDB;

Pt-duplicate-key-checker-udbuser-pdbpaswd -- charset = gbk \ -- database = dbname

After the execution, the duplicate and redundant indexes of the dbname database will be calculated as follows:

######################################## ################################## Dbname. test1 ####################################### ################################### vkey is a left- prefix of keydesc_index # Key definitions: # KEY 'vkey' ('vehiclekey'), # KEY 'keydesc _ Index' ('vehiclekey', 'description') # Column types: # 'vehiclekey' char (8) not null default ''# 'description' char (255) not null default'' # To remove this duplicate index, execute: alter table 'dbname '. 'test1' drop index 'vkey '; ######################################## ################################## dbname. test2 ####################################### ################################### vkey is a duplicate PRIMARY # Key definitions: # KEY 'vkey' ('vehiclekey'), # primary key ('vehiclekey'), # Column types: # 'vehiclekey' varchar (8) not null default '0' # To remove this duplicate index, execute: alter table 'dbname '. 'test2 'drop index'vkey ';

It counts all repeated, redundant indexes, server space, and SQL statements to be executed. Is it convenient.

For details about all the parameters or usage of the tool, see pt-duplicate-key-checker.

This article is from the "Focus on the database" blog. Please keep this source