MongoDB data paging and sorting Limit,skip,sort users

This article first gives you some basic statements about MongoDB data paging and sorting Limit,skip,sort users, and then uses an example to detail the MongoDB data paging and sorting instance method.
-
-
Sort with sort, equivalent to order by, ascending by 1, descending by-1
For example, by the age word orderby order:

Code to copy code as follows
Db.user.find (). Sort ({"Age": 1})

If there are multiple fields, by name descending, age ascending

Code to copy code as follows
Db.user.find (). Sort ({"Name":-1, "Age": 1})

Limit and skip are used together, equivalent to the limit in MySQL:

Code to copy code as follows
B.user.find (). Skip (1). Limit (1)

Equivalent to the limit in MySQL, that is, skip No. 0, start from 1th, return only 1
Skip and limit can achieve paging, but skip too much, will affect performance, should try to avoid, for example, get the current page the last document of a value as a condition (such as date) to query, you can skip.

No matter what database you use, there are usually some common ways to do paging and ranking. This article introduces some test data to show you the performance of MongoDB in this respect.

Pagination First we do a paging, in MongoDB the sample data is not as follows:

Code to copy code as follows
Db.scores.find ();
{Lid:objectid ("4fe506dabb2bfa742d000001"), Score:1, Name: ' User_1 '}
{Lid:objectid ("4fe506dabb2bfa742d000001"), Score:2, Name: ' User_2 '}
{Lid:objectid ("4fe506dabb2bfa742d000001"), Score:3, Name: ' User_3 '}
{Lid:objectid ("4fe506dabb2bfa742d000001"), Score:4, Name: ' User_4 '}

Where the lid field is used to distinguish between different latitude, mainly used in the screening, in the test collection, a total of five different lid values, each corresponding 1,200,000 data, altogether 6,000,000 data. The indexes are on lid and score. (The following query can be used to index)

Then we perform the following performance tests:

Code to copy code as follows
Collection = mongo::connection.new.db (' Test '). Collection (' scores ')
BENCHMARK.BMBM do |x|
X.report ("MONGO small") do
100.times do |i|
Collection.find ({: lid = lids.sample}, {: Fields = {: _id = false,: score = true,: User = = true}}). sort ({: Score = 1}). Limit (.). Skip (I *). to_a
End
End
X.report ("MONGO medium") do
100.times do |i|
Collection.find ({: lid = lids.sample}, {: Fields = {: _id = false,: score = true,: User = = true}}). sort ({: Score = 1}). Limit (.). Skip (I * +). to_a
End
End
X.report ("MONGO large") do
100.times do |i|
Collection.find ({: lid = lids.sample}, {: Fields = {: _id = false,: score = true,: User = = true}}). sort ({: Score = 1}). Limit ((). Skip (I * 10000). to_a
End
End
End

Above, the number of skip bars is smaller, medium-sized and very large in the case of testing. And the limit specifies that the data obtained is the same as 20. The test results in these three cases were: 0.6 seconds, 17 seconds, 173 seconds.

As we can see, for MongoDB, the size of skip seriously affects performance, should be strictly avoid the special large skip operation.

Ranking ranking functions are similar to paging, unlike ranking by calculating the number of bars that are larger than a certain value.

Like what:

Code to copy code as follows
Sql
Select COUNT (*) from scores where lid = $ and score > $
Mongo
Db.scores.find ({lid:lid, score: {$gt: Score}}). Count ()

Because the rankings and pagination implementations are similar in principle, the results are virtually the same. The test results are as follows:

MONGO Top Rank 1.155847
MONGO Average 22.291007

Conclusion There is a comparison, so what is the point of this article?

First, in MongoDB, try to avoid larger skip operations, such as paging, if you can know the need to get the data of the previous score, then you may be able to use the following method to obtain the data you want, rather than through a large skip operation.

Db.scores.find ({lid:lid, score: {$lt: Last_score}}). Sort ({score:-1}). Limit (20) Also, if you need to perform a larger skip operation or count a larger number, Then consider using Redis's sorted sets to do

For more detailed information, please see: http://www.111cn.net/database/MongoDB/51017.htm