0

I'm newbie to mongodb and I need help about increasing performance for queries using compound indexes.

I am using pagination using "_id" field. Query:

db.data.find( {"$or": [ { "cat": "5" }, {"cat": "6" } ] }, {"style": "3"}, 
              {"area": "London"}, {"_id": {"$lt": 103}).sort( { "_id": 1 } ).limit(30)

style and area can be optional so these are also possible.

db.data.find( {"$or": [ { "cat": "5" }, {"cat": "6" } ] },  
              {"area": "London"}, {"_id": {"$lt": 103}).sort( { "_id": 1 } ).limit(30)

db.data.find( {"$or": [ { "cat": "5" }, {"cat": "6" } ] }, {"style": "3"}, 
              {"_id": {"$lt": 103}).sort( { "_id": 1 } ).limit(30)

db.data.find( {"$or": [ { "cat": "5" }, {"cat": "6" } ] }, 
              {"_id": {"$lt": 103}).sort( { "_id": 1 } ).limit(30)

Will these queries be ok with this compound index, or do I need more additional index?

{ "cat": 1, "style": 1, "area": 1, "_id": 1 }

Edit

I'm not sure which of these index would be efficient for queries.

{ "cat": 1, "style": 1, "area": 1, "_id": 1 } or
{ "_id": 1, "cat": 1, "style": 1, "area": 1 }

Number of styles: 16

Number of Areas: 50

Number of id: 10 million

Jun
  • 3,422
  • 3
  • 28
  • 58

1 Answers1

0

use 

{ "_id": 1, "cat": 1, "style": 1, "area": 1 }

ObjectId has this structure:

0 1 2 3   | 4 5 6   | 7 8 | 9 10 11
Timestamp | Machine | PID | Increment

because the timestamp comes first in above structure, it will sort in roughly insertion order.

When id came first in index, it will reduce your looking objects.

Disposer
  • 6,201
  • 4
  • 31
  • 38
  • As per mongodb documentation, the query which only includes _id, cat and area will be less efficient than index {"_id", "cat", "area"}. Is it better to create this another compound index? – Jun Dec 02 '14 at 08:19
  • And will it automatically fetch indexes from this new index for queries with only _id, cat, area? – Jun Dec 02 '14 at 08:20
  • if you use { "_id": 1, "cat": 1, "style": 1, "area": 1 } but in your query you are not using "area", the index works properly and efficient, but if you are not using "style" in query, index will not work efficiently – Disposer Dec 02 '14 at 08:24
  • Yes, so better to create new index {"_id":1, "cat":1, "area":1}? and all good? – Jun Dec 02 '14 at 08:26
  • I believe you find your answer in http://docs.mongodb.org/manual/tutorial/sort-results-with-indexes/ – Disposer Dec 02 '14 at 08:29
  • I think you're wrong. _id has to be last component. Please see here http://docs.mongodb.org/ecosystem/use-cases/product-catalog/ Note: The final component of the index is the sort field. This allows MongoDB to traverse the index in the sorted order to preclude a slow in-memory sort. – Jun Dec 03 '14 at 14:08
  • In your case, you wanna sort by id. id usually sorted physically. I believe it should be your first item to index. use find(..).explain() and look which index is best for you (by number of scannedObjects) and put the result in your post if its possible. – Disposer Dec 03 '14 at 15:31