The method of paging using LIMIT in MySQL

Today, I saw a water friend saying that his MySQL is changing very slowly. Ask what's going on. An MyISAM that says a single table has more than 2 G. What a rubbish way of answering.

Simple answer: change a powerful server. Change server very useful :)

......
Finally let the slow query get:

  SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N;
 
  SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N;

Such as:

SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;
 
  SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;

I was vomiting blood at the sight of this statement (BT's PHPWIND page, this statement is written by PHP beginners is normal, but PHPWIND is such a mature community still has this problem).
Let me briefly explain the principle of LIMIT. This is based on LIMIT N,M: LIMIT first looks for N+M rows, then takes M rows from N. So SQL like this should be an expensive overhead for 12755001 operations per query. For optimizations like LIMIT, the first goal is to make N as small or as unnecessary as possible.
How do you make this N as small as possible? All we can do is give a hint of paging by relative values. If we look at page 5 now, we want to look at page 6. Page 6 also shows 20 records. We can be sure that the daily value of page 6 is less than that of page 5. If the minimum daily value of page 5 is 2009-11-4, then we can use:

SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-1' ORDER BY postdate DESC LIMIT 20;
 
   SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-1' ORDER BY postdate DESC LIMIT 20;

Do this to query page 6. Also for viewing the contents of page 4 (assuming that the maximum date of page 5 is: 2009-11-3), the contents of page 4 are:

  SELECT * FROM pw_gbook WHERE uid='48' and postdate>'2009-11-3' ORDER BY postdate DESC LIMIT 20;


   SELECT * FROM pw_gbook WHERE uid='48' and postdate>'2009-11-3' ORDER BY postdate DESC LIMIT 20;

This is a basic idea. Let's move on to the first question of how to present it.

Let's talk about how SQL for this kind of business works: you can use multi-purpose types for the presentation of paging. Here are three common types:

Type 1: Display the type "up" and "down"

It's relatively simple and that's where we get the kind of unthinking SQL writing that we see. Reasonable Practice:

Page 1:

SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 20;
 
   SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 20;

Page 2: Query according to postdate on page 1, as follows:
SELECT * FROM pw_gbook WHERE uid='48' and postdate & lt;'2009-11-3' ORDER BY postdate DESC LIMIT 20;

SELECT * FROM pw_gbook WHERE uid='48' and postdate & lt;'2009-11-3' ORDER BY postdate DESC LIMIT 20;

Why is that easy? There's no problem with page hopping. Then there is the 1 page jump problem.

Type 2: say "1,2,3,4,5..."

Page 1: Or as page 1:

 SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 20;
 
     SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 20;

Page 2: Same as the original. If you skip from page 2 to page 5, here is a page with a minimum date of 2009-11-3 (assumed value, can be obtained by the program on page 2), page 2 to 5, 2 more than 20 records per page, then you can use:

SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-3' ORDER BY postdate DESC LIMIT 40 . 20;


SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-3' ORDER BY postdate DESC LIMIT 40 . 20;

See here to understand why the large web page is not 1 under the identification of the end, let all can point it. It's not going to give you a box to type in and a page to skip over. If you skip too many pages, you have a problem with too much N. So if you want to do something, you must avoid it.

Type 3: "1,2,3,4,5... .Back page "or" front page, < < 100,101,102,103 > > At the end of the page"

Here's a special place:

Other page jump of the top 1. Here is to add a page at the end of two cases again here, if you know the final 1 page is how many pages, also to know the minimum 1 page before date (paging prompt values), so that you can use the above method to check the last page 1 (will appear the phenomenon of less than 20), the other one, I don't know is which last page, I just want to see what the final, you can use (1 set is display article 20) :

SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate ASC limit 20;
 
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate ASC limit 20;

Home here is not said.

Once you understand how to implement PHP, you can modify the PHP code. A slight modification of 1 will bring unexpected results.

Here is just one common method for paging. Different businesses may have different approaches. between... between... and .. Tape replaces limit paging operations.

The third method:
Simple logical transformations.

SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;
 
  SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;

Converted to:

SELECT * FROM pw_gbook WHERE id>1275480 and uid='48' ORDER BY postdate DESC LIMIT 20;
 
  SELECT * FROM pw_gbook WHERE id>1275480 and uid='48' ORDER BY postdate DESC LIMIT 20;