MySQL, SQL Server, Oracle three major database paging query (MySQL paging cannot use top, because not supported)

First, MySQL database paging query
MySQL database implementation paging is relatively simple, providing the limit function. It is generally only necessary to write directly to the SQL statement immediately after the line.
The limit clause can be used to limit the number of data returned by the SELECT statement, which has one or two parameters, if two arguments are given, the first parameter specifies the position of the first row returned in all data, starting with 0 (note not 1), and the second parameter specifies the maximum number of rows to return. For example:
SELECT * FROM Table WHERE ... LIMIT 10; #返回前10行
SELECT * FROM Table WHERE ... LIMIT 0, 10; #返回前10行
SELECT * FROM Table WHERE ... LIMIT 10, 20; #返回第10行开始 Total 20 rows of data

For MySQL tables with large data volumes, there are serious performance issues with limit paging.

Query 30 records from the 1 millionth after:

SQL code 1: Average spents 6.6 seconds SELECT * from ' cdb_posts ' ORDER by PID LIMIT 1000000, 30

SQL code 2: Average spents 0.6 seconds SELECT * from ' cdb_posts ' WHERE pid >= (SELECT pid from ' cdb_posts ' ORDER by PID LIMIT 1000000, 1) LIM IT 30

Because to take out all the field content, the first one needs to span a large chunk of data and take it out, while the second basically takes out the content by directly locating it based on the index field, which naturally boosts efficiency.

As you can see, the farther back the page is, the greater the offset of the limit statement, and the more obvious the speed gap will be.

second, SQL Server database paging query
SQL Server database is also divided into SQLServer2000 and SQLServer2005. A simple way to do this is through the top function. As follows:
SELECT TOP ten * from SQL WHERE (
Code not IN (SELECT TOP of code from testtable ORDER by id))
ORDER by ID
This statement, in theory, the execution time of the whole statement should be longer than the execution time of the clause, but the opposite is true. Because the clause executes after 20 records are returned, and the entire statement returns only 10 statements, the most important factor that affects the database response time is physical I/O operations. One of the most effective ways to limit physical I/O operations here is to use the top keyword. The top keyword is a system-optimized word in SQL Server that extracts previous or previous percentage data.
There is a fatal disadvantage of the above statement, that is, it contains not the word, to replace with not exists instead of not, the efficiency of the implementation of the two is no difference.
Of the above paging algorithms, the key factors that affect our query speed are two points: top and not. Top can improve our query speed, and not in will slow down our query speed, so to improve the speed of our entire paging algorithm, we need to completely transform not in, and other methods to replace it.
We know that in almost any field, we can extract the maximum or minimum value in a field by Max (field) or min (field), so if this field is not duplicated, you can use the max or min of these non-repeating fields as a watershed to make it a reference for separating each page in the paging algorithm. Here, we can use the operator ">" or "<" to accomplish this mission. Such as:

Select Top Ten * from table1 where id>200

So there is the following paging scheme:

Select Top Page Size *

From table1

where id>

(select Max (ID) from

(Select Top ((page 1) * page size) ID from table1 ORDER by ID) as T

)

ORDER BY ID

How much of this method does not always have a big drop, the stamina is still very sufficient. Especially when the data volume is large, the execution speed of the method is not reduced at all.
Using top requires that the primary key must be unique and cannot be a federated primary key. If it is a federated primary key, the query results are out of order.
Currently, SQLSERVER2005 provides a row_number () function. Row_number () is the generation of a sequential line number, and the standard in which he generates the order is the over-(order by ReportID) immediately following it, where ReportID can be a federated primary key. Below, let's see how to apply this rowno to the page in detail.
SELECT TOP Ten * from
(
SELECT top Row_number () over (ORDER by ReportID) as RowNo
From TABLE
) as A
WHERE RowNo > "+ pageindex*10
PageIndex is the number of pages we need for data.

But for SQLServer2000, if it is the joint primary key, I have no solution, if you have to contact me. Thank you, everyone.

third, Orcale database paging query

SELECT * FROM
(
SELECT a.*, ROWNUM RN
From (SELECT * from table_name) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

The most internal query, select * FROM table_name, represents the original query statement without paging. ROWNUM <= 40 and RN >= 21 control the range of pages per page for paged queries.

MySQL, SQL Server, Oracle three major database paging query (MySQL paging cannot use top, because not supported)