0

Let's say I have a MongoDB's database with millon of records having a City name, a User name and an activity.

City        | User   | Action
------------+--------+-------------------
Gotham      | Batman | Fight with Joker
Gotham      | Batman | Fight with Penguin
Gotham      | Robin  | Fight with Joker
Gotham      | Robin  | Fight with Riddler
Springfield | Homer  | Working
Springfield | Homer  | Moe's bar
Springfield | Bart   | At school

I want a fast paging method to show data in a web site listing all results, results by city or results by city and user. Although I have the main indexes by city & name, still using the db.find(...).skip((page_number - 1) * rows_per_page).limit(rows_per_page) method is slow.

Because no record is deleted, my approch to circumvent the performance issue, is to add some extra rows with incremental values and several new indexes:

City        | User   | Action             | Global Index | Index By City | Index By City & User
------------+--------+--------------------+--------------+---------------+---------------------
Gotham      | Batman | Fight with Joker   | 1            | 1             | 1
Gotham      | Batman | Fight with Penguin | 2            | 2             | 2
Gotham      | Robin  | Fight with Joker   | 3            | 3             | 1
Gotham      | Robin  | Fight with Riddler | 4            | 4             | 2
Springfield | Homer  | Working            | 5            | 1             | 1
Springfield | Homer  | Moe's bar          | 6            | 2             | 2
Springfield | Bart   | At school          | 7            | 3             | 1

So I do paging by Global Index, City + Index By City or City + User + Index By City & User

Is this approach good or horrible? Or is there a better method?

  • You don't have an existing field which stores the date and time of record insertion that you could use instead? – Colin 't Hart Sep 20 at 8:26
  • Does the explain plan of the slow query show it using the index? – Joe Sep 20 at 9:28
  • Hi @Colin'tHart I could, but data is not inserted at a constant rate. – Mauro H. Leggieri Sep 20 at 15:15
  • Hi @Joe using the index works But real data has several fields and paginated views so I have to apply a similar approach to all of the views which, in turn, leads to having several "sequential index fields" and indexes on them. The method works but would like to know if there is a different approach than this sort of hack hack. – Mauro H. Leggieri Sep 20 at 15:19
  • 1
    There is a limit of 64 indexes per collection. Yes, inserting/deleting a document or updating an indexed field also requires updating each index, which is a non-zero cost. I suspect that the number of times a document is written is significantly less than the number of times it is read, so indexing should be an overall win for performance. – Joe Sep 25 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.