Optimize LIMIT offset

Wherever LIMIT offset is used for large tables, brakes start sooner or later. View requests

SELECT * FROM test_table ORDER BY id LIMIT 100000, 30 

can be executed for a very long time. For example, in my case, at one of the sites, the number of comments exceeded 200k and page-by-page navigation through comments began to slow down noticeably, and in mysql-slow.log more and more requests with a runtime of 3-5 seconds began to fall.

The problem is that using LIMIT 100000, 30 - mysql first traverses the first 100000 records and only then selects the required 30. It’s quite simple to avoid this; it’s enough to use a view subquery, which generally looks like this:

 SELECT * FROM test_table JOIN (SELECT id FROM test_table ORDER BY id LIMIT 100000, 30) as b ON b.id = test_table.id 

Let's look at a specific example. In my case, the DLE engine is used and the query in it looks like this:

 SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id ORDER BY id desc LIMIT 101000,30 

The corrected request looks like this:

 SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id JOIN (select id FROM dle_comments ORDER BY id desc LIMIT 101000,30 ) as t ON t.id = dle_comments.id 

On the graph you can see the result of this replacement:


As you can see, using the JOIN, performance is kept at the desired level, regardless of how far the user has climbed into the wilds of the site using page-by-page navigation.
')
Ps. Fix for DLE for comments (in the same way you can do for all navigation). In the comments.class.php file
to find

 $sql_result = $this->db->query( $this->query . " LIMIT " . $this->cstart . "," . $this->comments_per_pages ); 

replace this line with:

 if( $_GET['do'] == "lastcomments" ) $sql_result = $this->db->query( str_replace("ORDER BY id desc", "JOIN (select id FROM " . PREFIX . "_comments ORDER BY id desc" . " LIMIT " . $this-else $sql_result = $this->db->query( $this->query . " LIMIT " . $this->cstart . "," . $this->comments_per_pages ); >cstart . "," . $this->comments_per_pages .") as t ON t.id = " . PREFIX . "_comments.id",$this->query) );