4

I need some advice in creating and ordering indexes in mongo.

I have a post collection with 5 properties:

Posts

  • status
  • start date
  • end date
  • lowerCaseTitle
  • sortOrder

Almost all the posts will have the same status of 1 and only a handful will have a rejected status. All my queries will filter on status, start and end dates, and sort on sortOrder. I also will have one query that does a regex search on the title.

Should I set up a compound key on {status:1, start:1, end:1, sort:1}? Does it matter which order I put the fields in the compound index - should I put status first in the compound index since it's the most broad? Is it better to do a compound index rather than a single index on each property? Does mongo only use a single index on any given query?

Are there any hints for indexes on lowerCaseTitle if I'm doing a regex query on that?

sample queries are:

db.posts.find({status: {$gte:0}, start: {$lt: today}, end: {$gt: today}}).sort({sortOrder:1})

db.posts.find( {lowerCaseTitle: /japan/, status:{$gte:0}, start: {$lt: today}, end: {$gt: today}}).sort({sortOrder:1})
MonkeyBonkey
  • 46,433
  • 78
  • 254
  • 460

1 Answers1

17

That's a lot of questions in one post ;) Let me go through them in a practical order :

  • Every query can use at most one index (with the exception of top level $or clauses and such). This includes any sorting.
  • Because of the above you will definitely need a compound index for your problem rather than seperate per-field indexes.
  • Low cardinality fields (so, fields with very few unique values across your dataset) should usually not be in the index since their selectivity is very limited.
  • Order of the fields in your compound index matter, and so does the relative direction of each field in your compound index (e.g. "{name:1, age:-1}"). There's a lot of documentation about compound indexes and index field directions on mongodb.org so I won't repeat all of it here.
  • Sorts will only use the index if the sort field is in the index and is the field in the index directly after the last field that was used to select the resultset. In most cases this would be the last field of the index.

So, you should not include status in your index at all since once the index walk has eliminated the vast majority of documents based on higher cardinality fields it will at most have 2-3 documents left in most cases which is hardly optimized by a status index (especially since you mentioned those 2-3 documents are very likely to have the same status anyway).

Now, the last note that's relevant in your case is that when you use range queries (and you are) it'll not use the index for sorting anyway. You can check this by looking at the "scanAndOrder" value of your explain() once you test your query. If that value exists and is true it means it'll sort the resultset in memory (scan and order) rather than use the index directly. This cannot be avoided in your specific case.

So, your index should therefore be :

db.posts.ensureIndex({start:1, end:1})

and your query (order modified for clarity only, query optimizer will run your original query through the same execution path but I prefer putting indexed fields first and in order) :

db.posts.find({start: {$lt: today}, end: {$gt: today}, status: {$gte:0}}).sort({sortOrder:1})
Remon van Vliet
  • 18,365
  • 3
  • 52
  • 57
  • 1
    Sort keys should not always be after the last key used for query. If a sort key is used early, the sorting happens before the pruning. [This post](http://architects.dzone.com/articles/cardinal-ins-mongodb-query) illustrates why this is useful. – Anuj Gupta Jun 30 '13 at 00:21
  • Interesting. I'll do a few tests to have a look at actual real world performance characteristics. I have to say I'm somewhat skeptical at this ever being a performance improvement in real world scenarios (read; large data sets,high selectivity of range query) – Remon van Vliet Jul 04 '13 at 09:54
  • @RemonvanVliet with regard to cardinality and disregarding index prefix optimization, should compound index keys be sorted by highest to lowest cardinality or lowest to highest? i went through mongodb docs referring to compound indexes and came up empty with an answer to this specific question; i'm assuming highest to lowest though. also: as of 2.6 mongodb supports 2 indexes with index intersection. – zamnuts Jan 19 '15 at 21:18
  • @zamnuts Good question. I've never noticed a significant difference and I've actually tested performance. I would assume higher selectivity early on would help overal performance but I haven't tried large enough datasets to make the measurements reliably. – Remon van Vliet Jan 21 '15 at 16:50
  • 1
    According to the documentation Mongo does support usage of multiple indexes in one query since version 2.6: https://docs.mongodb.com/manual/core/index-intersection/ – user1587520 Jul 20 '17 at 12:12