Using LIMIT in MySQL for paging, mysqllimit for paging

Using LIMIT in MySQL for paging, mysqllimit for paging

Today, a friend of mine said that his MySQL has become very slow. When asked. Indicates a MyISAM with a single table exceeding 2 GB. The answer to the spam.

Simple answer: change to a strong server. It works very well for changing servers :)

.........
Finally, the slow query is obtained:
 

  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;

For example:

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 have vomited blood when I saw this statement. (The PHPWIND page of BT is still normal for beginners of PHP, but this problem still exists in the mature community of PHPWIND ).
Let me briefly introduce the principle of LIMIT. Here we use limit n and M as the basis: LIMIT first needs to query N + M rows, and then take M rows from N rows. Therefore, such an SQL statement may be expensive to query 1275500 operations at a time. For LIMIT optimization, the first goal is to make N as small or unnecessary as possible.
How can we make N as small as possible. What we can do is to use the relative value to give a prompt to the page. For example, we are looking at page 5th. If you want to see page 6th, the page 6th also shows 20 records. We can think of this example as follows: we can be certain that the Daily Value of page 6th should be smaller than page 5th, if the minimum daily value of page 5th is, 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;

In this way, you can query the content on the 6th page. Similarly, for viewing the content of page 4th (assuming the maximum Date of page 5th is 2009-11-3), the content of page 4th is:
 

  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. Next we will discuss how to present the problem.

Let's talk about how to implement this kind of business SQL: the multi-purpose type can be used for paging display. Three Common types are described here:

First: display the type of "Previous Page" and "next page"

This method is relatively simple, and we see that SQL does not think about writing. 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 by postdate on the first page, for example:
SELECT * FROM pw_gbook WHERE uid = '48' and postdate <'2017-11-3 'order by postdate desc limit 20;
 
SELECT * FROM pw_gbook WHERE uid = '48' and postdate <'2017-11-3 'order by postdate desc limit 20;

Why is this simple? There is no page Jump problem. Next, there is a page Jump problem.

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

Page 1: It is implemented in the first page:

 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 before. If the page jumps, for example, from the second page to the 5th page, the minimum date of the second page is: (assuming the value can be queried by the Program on the second page), and the second to 5th, if there are two pages with 20 records on each page, 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;

I can see why the paging of a large website is not marked as complete. It won't show you a box for you to enter a page to jump over. If too many page jumps, the N value is too large. Therefore, it is necessary to find a solution.

Third: displays "1, 2, 3, 4, 5 ,.... Last page or homepage, <100,101,102,103> last page"

Here is a special example:

The jump to other pages is the same as above. Here we add a last page. There are two cases. If you know how many pages the last page is, you will know the minimum date of the previous page (the paging prompt value ), in this way, you can use the above method to view the content of the last page (there will be less than 20 Results). On the other hand, I don't know the last page, I just want to see what it looks like at the end, so I can use it (it must be 20 entries ):

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;

The homepage is not mentioned here.

After understanding how to implement the code, you can modify the PHP code. A slight modification will bring unexpected results.

 

This is just a common paging method. Different services may have different processing methods. If the conditions and conditions are possible, use... And... takes the place of limit paging operation.

Method 3:
Simple logic conversion.

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;

Convert:

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;