Limit optimization for MySQL paging

1, many new people will be very puzzled, why I built the index to use paging or so card. OK, now let's take one step at a time to find out why.

First, the limit itself is not directly related to the index.

Build a list of commodity SKUs first

CREATE TABLE Goods_sku
(
ID Int (TEN) unsigned not null auto_increment comment ' self-increment id ',
goods_id varchar (a) NOT NULL comment ' commodity ID ',
Sale_status tinyint comment ' Upper shelf status (0 shelves, 1 shelves) ',
Added_time timestamp not NULL DEFAULT current_timestamp COMMENT ' Home Date ',
Drop_time timestamp not NULL default ' 0000-00-00 00:00:00 ' COMMENT ' shelf time ',
' Is_del ' tinyint (4) not NULL DEFAULT ' 0 ' COMMENT ' delete tag (0 not deleted 1 delete) ',
KEY ' index_goods_id ' (' goods_id '),
KEY ' Index_sale_status ' (' Sale_status '),
KEY ' Index_added_time ' (' Added_time '),
Primary KEY (ID)
) Comment = ' Commodity SKU table ' Engine=innodb DEFAULT Charset=utf8;

Mysql> Explain select * from Goods_sku limit 0, 10;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| 1 | Simple | Goods_sku | All | NULL | NULL | NULL | NULL | 107950 | |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
1 row in Set (0.00 sec)

PS: Because I did not walk the index, so a full table scan, now is 100,000 data, just imagine how the 1 million case. A simple SQL will make your machine explode. I want a piece of data now, use the index to see

mysql> Explain select * from Goods_sku where Sale_status=1 limit 0,10;
+----+-------------+-----------+------+-------------------+-------------------+---------+-------+---
---+--- ----------+
| id | select_type | table     | type | possible_keys     | key&nb sp;              | Key_len | ref   | RO
ws | extra       |
+----+-------------+-----------+------+-------------------+-------------------+---------+-------+---
--- +-------------+
|  1 | simple      | Goods_sku | ref  | Index_sale_status | Index_sale_status | 2       | Const |
25 | Using where |
+----+-------------+-----------+------+-------------------+-------------------+---------+-------+---
--- +-------------+
1 row in Set (0.10 sec)

Although the index was gone, the number of affected bars was more than 4,000.

Mysql> Explain select * FROM Goods_sku ORDER BY id desc limit 0, 10;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------+
| 1 | Simple | Goods_sku | Index | NULL | PRIMARY | 8 |   NULL |       10 | |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------+
1 row in Set (0.00 sec)

This affected condition is 10, and it appears that limit and order by are used to actually limit the number of outputs, but the field after order by must be indexed

Limit optimization for MySQL paging