Avoid high offsets in queries

[macbre]

https://www.percona.com/blog/2008/09/24/four-ways-to-optimize-paginated-displays/

High OFFSET values cause all rows to be processed by MySQL and only the last ones to be returned. Use primary key column and id > value condition.

SELECT /* CategoryPaginationViewer::processSection */  page_namespace,page_title,page_len,page_is_redirect,cl_sortkey_prefix  FROM `page` INNER JOIN `categorylinks` FORCE INDEX (cl_sortkey) ON ((cl_from = page_id))  WHERE cl_type = 'page' AND cl_to = 'Spotify/Song'  ORDER BY cl_sortkey LIMIT 927600,200

Applications that paginate tend to bring the server to its knees. In showing you a page of results, with a link to go to the next page, these applications typically group and sort in ways that can’t use indexes, and they employ a LIMIT and offset that cause the server to do a lot of work generating, then discarding rows.

Explains

mysql> explain select * from 0020_big_table order by id limit 5;
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | 0020_big_table | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | NULL  |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0,00 sec)

-- notice increased number of rows
mysql> explain select * from 0020_big_table order by id limit 50, 5;
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | 0020_big_table | NULL       | index | NULL          | PRIMARY | 4       | NULL |   55 |   100.00 | NULL  |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0,00 sec)

A better approach with primary key column condition

mysql> select * from 0020_big_table order by id limit 50, 5;
+----+-----+-------+
| id | val | text  |
+----+-----+-------+
| 51 |  11 | 00033 |
| 52 |  11 | 00034 |
| 53 |  11 | 00035 |
| 54 |  11 | 00036 |
| 55 |  11 | 00037 |
+----+-----+-------+
5 rows in set (0,00 sec)

-- vs

mysql> select * from 0020_big_table where id > 50 order by id limit 5;
+----+-----+-------+
| id | val | text  |
+----+-----+-------+
| 51 |  11 | 00033 |
| 52 |  11 | 00034 |
| 53 |  11 | 00035 |
| 54 |  11 | 00036 |
| 55 |  11 | 00037 |
+----+-----+-------+
5 rows in set (0,00 sec)

Bad

mysql> select * from 0020_big_table order by id limit 19000, 5;
+-------+------+-------+
| id    | val  | text  |
+-------+------+-------+
| 19001 | 3801 | 04a39 |
| 19002 | 3801 | 04a3a |
| 19003 | 3801 | 04a3b |
| 19004 | 3801 | 04a3c |
| 19005 | 3801 | 04a3d |
+-------+------+-------+
5 rows in set (0,01 sec)

mysql> select * from 0020_big_table order by id limit 5 offset 19000;
+-------+------+-------+
| id    | val  | text  |
+-------+------+-------+
| 19001 | 3801 | 04a39 |
| 19002 | 3801 | 04a3a |
| 19003 | 3801 | 04a3b |
| 19004 | 3801 | 04a3c |
| 19005 | 3801 | 04a3d |
+-------+------+-------+
5 rows in set (0,00 sec)

mysql> explain select * from 0020_big_table order by id limit 19000, 5;
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | 0020_big_table | NULL       | index | NULL          | PRIMARY | 4       | NULL | 19005 |   100.00 | NULL  |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0,00 sec)

Better

mysql> select * from 0020_big_table where id > 19000 limit 5;
+-------+------+-------+
| id    | val  | text  |
+-------+------+-------+
| 19001 | 3801 | 04a39 |
| 19002 | 3801 | 04a3a |
| 19003 | 3801 | 04a3b |
| 19004 | 3801 | 04a3c |
| 19005 | 3801 | 04a3d |
+-------+------+-------+
5 rows in set (0,00 sec)

mysql> explain select * from 0020_big_table where id > 19000 limit 5;
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | 0020_big_table | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 48944 |   100.00 | Using where |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0,00 sec)

LIMIT is not taken into account while estimating number of rows Even if you have LIMIT which restricts how many rows will be examined MySQL will still print full number. Here is example:

https://www.percona.com/blog/2006/07/24/mysql-explain-limits-and-errors/