TODO: paging for database optimization

TODO: paging for database optimization

The example of this article is based on the MongoDB database, other databases can also be optimized.

Paged use in MongoDB

A.skip (n) skips the first n matching documents

B.limit (m) returns m results. If there are less than m matching results, it returns the result of matching the amount of data. M is the specified upper limit, not the lower limit.

C.sort ({"name": 1, "address":-1}), where 1 indicates ascending order and-1 indicates descending order.

It's OK to skip a small number of documents using skip. However, if the amount of data is very large, skip will become very slow, as will be the case in every database, so try to avoid using skip too much. So how to do paging, we can use the results of the last time to calculate the next query.

1. Paging using skip

Page1 = db.user.find ({}) .limit (100)

Page2 = db.user.find ({}) .skip (100) .limit (100)

Page3 = db.user.find ({}) .skip (200) .limit (100)

two。 Use the last result to calculate the next query, sorted by time stamp (timestamp)

Get the first page

Page1 = db.user.find ({}) .sort ({"timestamp":-1}) .limit (10)

Gets the timestamp lasttimestamp of the last record on the current page

Query the next page of data according to lasttimestamp

Nextpage=db.user.find ({"timestamp": {"$gt": lasttimestamp}}) .sort ({"timestamp":-1}) .limit (10)

This way the query does not use skip, but make sure that the timestamp unique constraint ensures that the data in the document does not have the same value.

Wxgzh:ludong86