MongoDB and PHP with pagination, distinct values (Performance issues)

Easiest way to do filtered results and pagination is as follows:

$cursor = $lib_collection->find($filter_params, array())
$count = $cursor->count();
$data = $cursor->skip(20)->limit(20);

However, this method may not be somewhat inefficient. If you query on fields that are not indexed, the only way for the server to "count()" is to load each document and check. If you do skip() and limit() with no sort() then the server just needs to find the first 20 matching documents, which is much less work. Remember that skip() and take() --no matter if you use an index or not-- will have to perform a scan. Therefore, skipping very far is very slow.

The number of results per category is going to be more difficult.

Think of it this way: The database has an index (B-Tree) that can compare values to each other: it can tell you quickly whether something is bigger or smaller than some given x. Hence, search times in well-balanced trees are logarithmic. This is not true for count-based indexation: A B-Tree has no way to tell you quickly what the 15.000th element is: it will have to walk and enumerate the entire tree.

If the data does not change often, you may want to precalculate these values using regular map/reduce jobs. Otherwise you have to run a series of distinct() commands or in-line map/reduce. Neither one is generally intended for ad-hoc queries.

The only other option is basically to load all of the search results and then count on the webserver (instead of the DB). Obviously, this is also inefficient.

Getting all of these features is going to require some planning and tradeoffs.

SQL is better optimized for "set-based" queries such as aggregation, where MongoDB is optimized for key-value lookups.One of the MongoDB "trade-offs" is that it is not good at real-time aggregation queries. Both styles of DB can do both things, but with different performance. Note, if you use $cursor->count() on something indexed, your response will be faster (true in all DBs) you may want to try this optimization.

Post Tags:

mongodb-and-php

Sael

I'm Sael. An expert coder and system admin. I enjoy to make codes easy for novice.

Website: fb/Fujael