MySQL indexes optimize distinct statements and distinct multi-field operations, mysqldistinct

MySQL indexes optimize distinct statements and distinct multi-field operations, mysqldistinct

MySQL usually uses GROUPBY (essentially a sorting action) to perform the DISTINCT operation. If the DISTINCT operation and ORDERBY operation are combined, a temporary table is usually used. this will affect the performance. in some cases, MySQL can use indexes to optimize the DISTINCT operation, but it needs to be learned and used. this document describes an example of a DISTINCT operation that cannot be performed using an index.

 

Instance 1 optimize the DISTINCT operation using Indexes

create table m11 (a int, b int, c int, d int, primary key(a)) engine=INNODB;insert into m11 values (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8,8,8,8);explain select distinct(a) from m11;

mysql> explain select distinct(a) from m11;

Copy codeThe Code is as follows:

+ ---- + ------------- + ------- + ------------ + ------- + ------------- + --------- + ------ + ---------- + --------------- + | Id | select_type | table | partitions | type | keys | key_len | ref | rows | filtered | Extra | + ---- + ------------- + ------- + ------------ + ------- + --------------- + --------- + ------ + ---------- + ------------- + | 1 | m11 | NULL | index | PRIMARY | 4 | NULL | 1 | 100.00 | Using index | + ---- + ------------- + ------- + ------------ + ------- + ------------- + --------- + ------ + ------ + ---------- + ------------- +

Note:
One 'A' column has a primary key index. MySQL can use the index (the key column value indicates that the primary key index is used) to complete the DISTINCT operation.

2. This is a typical example of optimizing the DISTINCT operation using indexes.

 

Instance 2 using indexes cannot optimize the DISTINCT operation

create table m31 (a int, b int, c int, d int, primary key(a)) engine=MEMORY;insert into m31 values (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8,8,8,8);explain select distinct(a) from m31;

 mysql> explain select distinct(a) from m31;

Copy codeThe Code is as follows:

+ ---- + ------------- + ------- + ------------ + ------ + --------------- + ------ + --------- + ------ + ---------- + ------- + | Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ---- + --------------- + ------------ + ------ + --------------- + ------ + --------- + ------ + ---------- + ------- + | 1 | SIMPLE | m31 | NULL | ALL | NULL | 8 | 100.00 | NULL | + ---- + ------------- + ------- + ---------- + ------ + ------------- + ------ + --------- + ------ + ---------- + ------- +

Note:
1. According to the query execution plan, the index is not used.

2. Compare the table creation Statement of instance 1, except that the storage engine is different.

3 Why does the primary key index not work? Is indexes on the MEMORY storage engine unavailable?

 

Instance 3 use indexes to optimize Memory tables for DISTINCT operations

create table m33 (a int, b int, c int, d int, INDEX USING BTREE (a)) engine=MEMORY;insert into m33 values (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8,8,8,8);explain select distinct(a) from m33;

 

 mysql> explain select distinct(a) from m33;

+ ---- + ------------- + ------- + ------------ + ------- + ------------- + ------ + --------- + ------ + ---------- + ------- + | Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ---- + --------------- + ------- + ------------ + ------- + --------------- + ------ + --------- + ------ + ---------- + ------- + | 1 | SIMPLE | m33 | NULL | index | NULL | a | 5 | NULL | 8 | 100.00 | NULL | + ---- + ------------- + ------- + ------------ + ------- + --------------- + ------ + --------- + ------ + ---------- + ------- +
Note:
One 'A' column has a primary key index. MySQL can use the index (the key column value indicates that the primary key index is used) to complete the DISTINCT operation.

2. Compare instance 2, you can find that both use the Memory engine, but instance 3 uses the B-tree index.

3. instance 2 does not specify the type of index to be used. MySQL will use the default value, as described in the MySQL manual:

As indicated by the engine name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast for single-value lookups, and very useful for creating temporary tables.

 

Conclusion:

1. Check the impact of indexes on queries. Pay attention to the index type.

2. HASH indexes are suitable for equivalent searches, but are not suitable for scenarios requiring ordering, while B-tree is suitable for ordered scenarios.

3. Check the query execution plan and find that the index is not used. You need to further investigate the index type.

 

Solution to DISTINCT failure to select multiple fields
In practical applications, we often need to select duplicate data from a table in the database. Generally, we use the DISTINCT function.

However, DISTINCT is only valid for one field, for example:

sql="select DISTINCT title from Table where id>0"

When we need to list another column in the data, for example:

sql="select DISTINCT title,posttime from Table where id>0" 

The result is not what we want, so we need another method to solve this problem.

The following are the SQL statements I have written. I don't know if they are good. I hope you can share them with others:

Statement 1:

sql = "Select DISTINCT(title),posttime From Table1 Where id>0"

Statement 2:

sql = "Select title,posttime From Table1 Where id>0 group by title,posttime"

Statement 3:

sql="select title,posttime from Table where id in (select min(id) from Table group by title)"

Articles you may be interested in:

• Basic Principles of distinct statements in MySQL and comparison with group by statements
• Query duplicate records using distinct statements in MySQL and Performance Discussion
• How to Use Distinct and Group By statements in MySQL
• Comparison of distinct and count (*) in MySQL
• Performance Comparison between distinct and group by in MySQL
• Tips for optimizing distinct in MySQL
• Parsing mysql: removing duplicate records for single-Table distinct and multi-Table group by queries
• How to Use distinct to query multiple unique record values in mysql
• Comparison and usage of distinct and group by statements in MySQL