Example analysis of the impact of MySQL index on sorting

This article uses examples to illustrate the impact of MySQL indexes on sorting. Share with you for your reference, the details are as follows:

Indexes can not only improve query speed, but also increase sorting speed. If the statement after order by uses the index, the sorting speed will be improved.

test

1. Create a test table: t15 table

CREATE TABLE `t15` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `cat_id` int(10) unsigned NOT NULL DEFAULT '0',
 `price` decimal(10,2) NOT NULL DEFAULT '0.00',
 `name` char(5) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. Insert 10,000 rows of data

<?php
$db = 'test';
$conn = mysql_connect('localhost','root','1234');
mysql_query('use ' . $db , $conn);
mysql_query('set names utf8' , $conn);
for($i=1;$i<=10000;$i++) {
  $cat_id = rand(1,10);
  $price = rand(1,50000);
  $name = substr(str_shuffle('abcdefghjkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ234565789'),0,5);
  $sql = sprintf("insert into t15 values ​​(%d,%d,%f,'%s')",$i,$cat_id,$price,$name);
  mysql_query($sql, $conn);
}

3. Shopping mall websites usually sort prices according to a certain category. Let’s simulate it and execute the same SQL statement.

select name,cat_id,price from t15 where cat_id=1 order by price;

(1) Do not add an index first

From the query analysis, we can see that Using filesort is used in Extra, indicating that file sorting is required.

(2) Add an index to the (cat_id, price) column

alter table t15 add index cp(cat_id,price);

In the query analysis here, Extra does not use Using filesort, which means that the SQL that adds the index and then performs the query does not use external sorting, but uses the index sorting. Because the index itself is sorted, there is no need for an additional order by.

4. Observation query time

The statement with Query_ID 1 is executed without adding an index, and the statement with Query_ID 3 is executed after adding an index. The execution time of one is 0.013 seconds, and the execution time of the other is 0.005 seconds. It is definitely faster with the index. Let's see where it is faster.

Detailed time-consuming graph of the SQL statement with Query_ID 1:

Detailed time consumption diagram for the SQL statement with Query_ID 3:

Obviously, Sorting result in Query_ID1 takes 0.012 seconds, while Sorting result in Query_ID2 only takes 0.000004 seconds, and this Sorting result is the sorting time.

Conclusion: The index has a certain impact on the speed of sorting. Therefore, in actual development, it is necessary to formulate the index based on the actual situation and add the sorting field to the index as much as possible.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.