MySQL efficient paging solution set sharing

  • 2020-06-03 08:34:57
  • OfStack

1. The most common basic paging method of MYSQL:

select * from content order by id desc limit 0, 10

In the case of small data volumes, such SQL is sufficient, and the only concern is to ensure that indexes are used. As the amount of data increases, the number of pages increases, and looking at the next few pages of SQL might look something like this:

select * from content order by id desc limit 10000, 10

The bottom line is that the further you page the LIMIT statement, the greater the offset and the slower the LIMIT statement will be.
At this point, we can do this in two ways:
1. Improve paging efficiency by means of sub-query. The easy-to-use SQL statement is as follows:

SELECT * FROM `content` WHERE id (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize

Why is that? Because subqueries are done on indexes, whereas normal queries are done on data files, which are generally much smaller than data files, it is also more efficient to operate. (via) via explain SQL statement found: subquery using index!

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY content range PRIMARY PRIMARY 4 NULL 6264 Using where
2 SUBQUERY content index NULL PRIMARY 4 NULL 27085 Using index

After the actual measurement by Piaoyi, the efficiency of using subquery paging mode is 14-20 times higher than LIMIT!
2, JOIN paging mode

select * FROM `content` AS t1
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id

In my tests, the efficiency of join paging and subquery paging is basically 1 level, and the elapsed time is also basically 1. explain SQL statement:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY system NULL NULL NULL NULL 1
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

3. Use the FOUND_ROWS() function of MYSQL
Mysql FOUND_ROWS() function combined with SQL_CALC_FOUND_ROWS in SELECT can get two results:
1. Get the contents of Limit
2. Get the number of all lines after Limit is removed
In SELECT statements it is often possible to use LIMIT to limit the number of rows returned. Sometimes you might want to know how many rows would be returned without LIMIT, but you don't want to execute the same statement again. Then, include the SQL_CALC_FOUND_ROWS option in the SELECT query, and then execute FOUND_ROWS() :

select SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

Where SQL_CALC_FOUND_ROWS tells Mysql to record the number of rows processed by sql, and FOUND_ROWS() takes this record. There are also two statements, but only one primary query is executed, so it is much more efficient than before.
1. If the SQL_CALC_FOUND_ROWS option is used in the previous statement, FOUND_ROWS() returns the number of rows returned when the first statement did not have LIMIT.
2. If the SQL_CALC_FOUND_ROWS option was not used in the previous statement, FOUND_ROWS() returns the actual number of rows returned in the previous statement.
If SELECT SQL_CALC_FOUND_ROWS is used, MySQL must count the number of rows for all result sets. Still, it would be faster than executing another query without LIMIT, because the result set would return the client drops. (Also: not only is the result set not returned, but it may also be that a laborious SQL such as LIKE does not need to be exhausted once more.)

--  Notice the condition in the following statement  LIKE
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE Name LIKE '%string%' id > 100 LIMIT 10;
SELECT FOUND_ROWS();


--  The above statement is equivalent to the following statement, but the performance improvement should be very, very significant: 
SELECT COUNT(*) FROM tbl_name WHERE Name LIKE '%string%' ;
SELECT * FROM tbl_name WHERE Name LIKE '%string%' id > 100 LIMIT 10;

Related articles: