Today, I saw a water friend say that his life is changing very slowly.When asking what is going on.Say a MyISAM with a single table more than 2 G.Really rubbish answer.

    Simple answer: switch to a stronger server.Changing servers works great :)

…………
       Finally get the slow query:
 

Example:
   

        I vomited blood when I saw this statement (BT's PHPWIND paging, this statement is written by PHP beginners is normal, but PHPWIND is so The mature community has such problems).
        Here I briefly talk about the principle of LIMIT.This is based on LIMIT N, M: LIMIT first finds N+M rows, and then takes M rows from N rows.Then such SQL should be an expensive overhead to query 1275500 operations at a time.For optimizations like LIMIT, the first goal is to make N as small as possible or not to use it.
     How to make this N as small as possible.What we can actually do is to use the relative value to give a hint to the pagination.For example, now we are looking at page 5.After reading it, we want to see page 6.The same page shows 20 records.We can think of this example: we can be sure that the daily value of page 6 should be less than that of page 5.If the minimum daily value of page 5 is: 2009-11-4, then we can use :
    

This will query the content of page 6.Also for viewing the content of page 4 (assuming the maximum date of page 5 is: 2009-11-3) then the content of page 4 is:
 

         This is a basic idea.Next, let's discuss how to show it.

         Here are three commonly used types:

Type 1: Displays the type ""

         This method is relatively simple, and we see that SQL does not think about writing.Reasonable practice:

         First page:
     

         Second page: query according to the postdate of the first page such as:
      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;

         Next, there is a page jump problem.

Second: Display "1,2,3,4,5..."

         The first page: still in the first page way:
        

         Second page: same as before.If jumping pages, such as jumping from the second page to the fifth page, there is a minimum date of the second page: 2009-11-3 (the hypothetical value, which can be obtained by the program query of the second page), the second to the first 5.There are 2 pages and 20 records per page, then you can use:

        See here to understand why the pagination of a large website is not marked out at once, so you can click it.It also doesn't give you a box to enter a page to skip over.If there are too many pages to jump, there is a problem that the N value is too large.So find a way to avoid it.

Third: Display "1, 2, 3, 4, 5,....last page" or "first page, <<100,101,102,103 >>last page"

There is a special place here:

The jump to other pages is the same as above.Here is a last page, and there are two cases here.If you know how many pages the last page is, you also know the minimum date (paging prompt value) of the previous page, so you can use the above method to view the last page.The content of the content (there will be less than 20), and the other, I don't know which page is the last, I just want to see what it looks like at the end, then it can be used (must show 20):

        

       

         If you understand the specific implementation, you can modify the PHP code.A little modification will bring unexpected results.

 

This is just a general pagination handling method.Different businesses may have different methods to deal with.If the conditions are possible and the situation can be tested: between...and..with instead of limit paging operation.

Third method:       
Simple logical transformation.
  

Transforms to: