MySQL counts total rows of records when paging and returns a fixed number of records using limit

　　The requirements are simple: Suppose there is a user table, there are actually 10,000 data in the table, but I do not know how many, I want to fetch 20 data from the database every time, then how to complete it?

Programme one:

First perform a

Select COUNT (*) as total from  user;  

　　　　The above SQL statement will detect the total number of records. Another point, perhaps here is not just unconditional check, if it is conditional check, then you can use the temporary table

Select COUNT (*) from user where id>10; or select count (*) from (select Id,name from user) as temp;

　　　　 After the total number of records has been calculated, you can use limit to take only a subset of the data:

SELECT * from user limit start,offset;

　　　　In fact, the above approach is completely no problem, but I have a question, the example here is only from a table to operate, but also very simple operation, if you want to do a very complex connection, grouping, deduplication, if the calculation of the data of a few tables is very large, then it means that the above operation is very redundant:

　　　　one is to perform a complete operation and then only the total number of records is obtained;

　　　　one is to perform the complete operation , and then only the offset bar data from the start is obtained.

In fact, the first step of the two operations is coincident, then I would like to be able to do only one operation (such as the connection between several tables, grouping, deduplication, etc.), and then can get the total number of records, and can only take start after the offset bar data

　　　　

　　Scenario Two:

MySQL counts total rows of records when paging and returns a fixed number of records using limit