Methods of paging query in various databases

In concrete implementation, according to the database used, the amount of data, the implementation of paging, the choice of paging to achieve a faster way to achieve.

First, MySQL paging Query method

MySQL paging query mainly uses its own limit function, but depends on the amount of queries to determine the specific use, such as only thousands of or tens of thousands of data, the direct use of the limit m,n way,

If the amount of data is large, pay attention to how the limit is used.

Limit m, N: Gets n data starting from the first m data

1, the way of less data: SELECT * FROM tablename limit m,n;

Limit m, n:m can be omitted, omitted or defaulted from No. 0 of the result set

2, the amount of data in a number of ways:

From 490000 onwards fetch 10

A. Select * FROM tablename where id>= (select ID from tablename limit 490000,1) limit 10;

B. Select * FROM tablename limit 490000,10;

A way faster than the B way

Second, DB2 paging Query method

DB2 Paging Query method is mainly implemented in the form of RowNumber () over () function, as follows:

SELECT * FROM (select field 1, Field 2...rownumber () over (order by id DESC) as RN from TableName where ...) as T1

Where T1.rn between M and N/t1.rn >m and T1.rn <= N;

Three, Oracle paging Query method

Oracle paging queries are implemented primarily by the RowNum keyword and the RowNumber () over () function, as follows:

1. rownum keyword--no sort--get N to M data

A. At the second level of the query, the maximum value is controlled by RowNum <= m, and the minimum value is controlled at the outermost of the query:

SELECT * FROM (

Select A.*, RowNum rn from (

SELECT * FROM TableName) a

where RowNum <= m)

where RN >= n

B. Control the maximum minimum value directly at the outermost layer

SELECT * FROM (

Select A.*, RowNum rn from (

SELECT * FROM tablename) a)

where RN between N and M

In most cases, a is much faster than the B method

2, rownum keyword--need to sort--get N to M data

Wrong way

SELECT * FROM

(Select T.*,rownum rn from MyTable t order by t.id) b

where B.row_num between N and M

Cause of error: SQL above executes rownum RN to generate line number, then order by ordering, but the final result is obtained through RN line number, order by sort does not work

Correct practice: Sort first, generate line number for sorted result, then get result according to RN obtained is sorted result

The right way: Add a second layer to sort first

SELECT * FROM

(select A.*, rownum rn from (SELECT * FROM tablename ORDER by ID) a) b

where B.rn between N and M

3, RowNum () over () way--the same DB2 way

SELECT * FROM (select field 1, Field 2...rownumber () over (order by id DESC) as RN from TableName where ...) as T1

Where T1.rn between M and N/t1.rn >m and T1.rn <= N;

Iv. SQL Server Paging Query method

SQL Server 2005 and later, can be implemented using rownum () over ()

The RowNum () over () mode version is not available with the top keyword

Select Top num * FROM TableName where .....

Five, MongoDB paging Query method

The paging query for MongoDB is paged by the three function combinations of limit (), skip (), sort ().

Slow when querying large amounts of data using the Skip method

page1 = db.things.find().limit(20)

    page2 = db.things.find().skip(20).limit(20)

    page3 = db.things.find().skip(40).limit(20)Find (query condition), skip (how many data to skip), limit (how many data to query), sort (sort by): sort ({"Age": 1}) positive sequence, -1--reverse-order mysql corresponding to MongoDB: query:

Mysql:select * from user

Mongo:db.user.find ()

Mysql:select * FROM user WHERE name = ' Starlee '

Mongo:db.user.find ({' name ': ' Starlee '})

Insert:

MySQL:INSERT inot User (' name ', ' age ') VALUES (' Starlee ', +)

Mongo:db.user.insert ({' name ': ' Starlee ', ' age ': +})

Delete:

MySQL:DELETE * from user

Mongo:db.user.remove ({})

MySQL:DELETE from user WHERE age <

Mongo:db.user.remove ({' age ': {$lt: +}})

Update:

MySQL: UPDATE user SET ' age ' = * * WHERE ' name ' = ' Starlee '

Mongo:db.user.update ({' name ': ' Starlee '}, {$set: {' Age ': $}})

MySQL:UPDATE user SET ' age ' = ' age ' + 3 WHERE ' name ' = ' Starlee '

Mongo:db.user.update ({' name ': ' Starlee '}, {$inc: {' Age ': 3}})

==============others=======================

MySQL:SELECT COUNT (*) from user WHERE ' name ' = ' Starlee '

Mongo:db.user.find ({' name ': ' Starlee '}). Count ()

MySQL:SELECT * from user limit 10,20

Mongo:db.user.find (). Skip (). Limit

MySQL:SELECT * from user WHERE "age" in (35,45)

Mongo:db.user.find ({' age ': {$in: [+/-]}})

MySQL:SELECT * from user ORDER by age DESC

Mongo:db.user.find (). Sort ({' Age ':-1})

MySQL:SELECT DISTINCT (name) from user WHERE age >

Mongo:db.user.distinct (' name ', {' age ': {$lt:}})

MySQL:SELECT name, sum (marks) from the user GROUP by name

Mongo:

Db.user.group ({

Key: {' name ': true},

Cond: {' name ': ' foo '},

Reduce:function (Obj,prev) {prev.msum + = Obj.marks;},

Initial: {msum:0}

});

Mysql:

SELECT name from user WHERE age < 20

Mongo:db.user.find (' This.age < ', {name:1})

Methods of paging query in various databases