Multiple-field operations for index-optimized distinct statements and distinct in Mysql _mysql

MySQL usually completes the distinct operation using groupby (essentially a sort action), and if the distinct operation is combined with the by-order operation, a temporary table is usually used. This can affect performance. In some cases, MySQL can use the index to optimize distinct operations, but it requires ingenious. This article covers an instance where the distinct operation cannot be performed using an index.

Instance 1 using index to optimize distinct operations

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 Code code as follows:

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+- ------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+------ ----+-------------+| 1 | Simple | M11 | NULL | Index | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+ ----------+-------------+

Description
There is a primary key index on the 1 ' A ' column, and MySQL completes the distinct operation using the index (the key column value indicates that the primary key index is used).

2 This is a typical example of using an index to optimize distinct operations.

Instance 2 using indexes does not optimize distinct operations

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 Code code as follows:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----- --+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------- +-------+| 1 | Simple | M31 | NULL | All | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+ -------+

Description
1 from the query execution plan, the index is not being used.

2 comparison of instance 1 of the build table statement, except that the storage engine is different.

3 Why does the primary key index not work? Isn't the index on the memory storage engine available?

Instance 3 using indexes to optimize the memory table 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 |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+---------- +-------+
Description
There is a primary key index on the 1 ' A ' column, and MySQL completes the distinct operation using the index (the key column value indicates that the primary key index is used).

2 Contrast Example 2, you can find that both use the memory engine. But instance 3 uses the index of the Btree type named.

3 Instance 2 does not specify what type of index to use, and MySQL takes the default value. The MySQL manual says:

As indicated by the engine name and 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 ary tables.

Conclusion:

1 See the impact of the index on the query, pay attention to the type of index.

2 hash index is suitable for the equivalent search, but it is not suitable for the ordered scene, but Btree is suitable for the orderly scene.

3 Look at the query execution plan and find that the index is not being used and need to examine the type of index further.

Distinct solution for multiple fields cannot be selected
in practical applications, we often have to select the data in a table in a database, and usually we use the distinct function.

But distinct can only be 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, such as:

Sql= "Select DISTINCT title,posttime from Table where id>0" 

The results are not what we want, so we need to solve the problem in a different way.

The following is the SQL statement I wrote, I do not know is not very good, I hope there are better people to share it:

Writing a:

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

Writing two:

sql = "Select Title,posttime from Table1 Where id>0 GROUP by Title,posttime"

Writing three:

Sql= "Select Title,posttime from table where id" (select min (id) to table Group by title) "