Mongodb Question posted in
The official documentation can be found here.

MongoDB two indexes with different directions

I have one index created

{
    v: 2,
    key: { xxxx: 1 },
    name: 'xxxx',
    background: true
},

But I have a lot of long queries with

  "sort": {
    "xxx": -1
  },

Will creating an additional index with -1 help anything, or can it be done that way at all?

createIndex({ "xxx": -1 })

This is a production database, so I can’t do too much before that if I know for sure if it will work

Tags:

2

Answers


  1. 0

    If it is taking too much time, as a initial checkup I’ll suggest these, since your question lacks a lot of context, I’m putting these for regular use case:

    1. Check for compound indexes, if your query has other fields then a coumpound index would help. .sort({ "xxx": -1 }).explain("executionStats") should help you find if correct index is being used.
    2. If you are returning a lot of data add limit
    3. If all queries are slow means index cluttered ie. remove the unused indexes that saves CPU and memory usage.
    4. Since your question shows lack of process to test before releasing, I’ll suggest adding similar data to staging/test environment, running the same there and verify, load testing is also a good option but just adding similar data from production to staging should be a quick win and allow you to test before making changes on production.

    Adding additional -1 index would for sure help with the specific query but having too many indexes should be avoided and might lead to slower DB with not much speed optimization.

    If you have mongo atlas, then the team might also support you, you’ll have to raise ticket with them.

    Login or Signup to reply.
  2. 0

    This answer assumes that the references to xxx and xxxx in the description are meant to be the same (redacted) field. Basically that it is a typo and they should have the same number of characters in all places.

    Will creating an additional index with -1 help anything, or can it be done that way at all?

    This will not help any query at all assuming there is an existing index present defined in the opposite (ascending) direction. In fact, adding that index can only make things worse, not better. It complicates the query planning process, slows down writes, etc.

    But I have a lot of long queries with

    "sort": {
   "xxx": -1
 }

    This question is a demonstration of the XY problem. You have asked us to opine about the anticipated effectiveness of your proposed solution to a problem that you are encountering, but you haven’t given us enough details about the actual problem that you are trying to solve actually is.

    The thing that I do agree with from the other answer is that it is more likely that you’ll be able to solve your actual problem by creating appropriate Compound Indexes. The fact that you commonly sort on the same field suggests that you may want to append it at after other equality predicates in the index definition. You can read more about this general topic on this page of the documentation.

    In order to suggest appropriate indexes we would need to see what the problematic queries are. All that we can confidently say based on the provided information is that the answer to your question is: "No, the index you proposed will not improve any query".

    Edit to add: One more thing worth mentioning regarding the explanation of why the index won’t help. Despite the mismatched directions between the requested sort and the index definition, the database is able to walk the index either forward or reverse. If your existing is being used for queries that have the sort then it is doing so by walking it backwards. This is fine and mentioned earlier in one of the pages I linked to, specifically here:

    For example, create an ascending index on the field a for a collection records:

    db.records.createIndex( { a: 1 } )

    …

    The index can also support the following descending sort on a by traversing the index in reverse order:

    db.records.find().sort( { a: -1 } )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search