Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-17643

Text indexes should work with standard indexes (instead of slowing them down)

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.6.8, 3.0.0
    • Component/s: Querying, Text Search
    • None
    • ALL
    • Hide

      1. Add a compound text index and standard ObjectId index into a collection with 1 million objects.
      2. Query by standard index, note normal speed.
      3. Query by compound text index, note speed (slower but still usable).
      4. Query by both at once, note that it is slower than either and will timeout on large enough result sets.

      Show
      1. Add a compound text index and standard ObjectId index into a collection with 1 million objects. 2. Query by standard index, note normal speed. 3. Query by compound text index, note speed (slower but still usable). 4. Query by both at once, note that it is slower than either and will timeout on large enough result sets.

      Currently, text indexes seem to interact very poorly with other indexes. In many cases with low result counts, adding a normally-indexed field will actually slow the search down significantly (which is counterintuitive).

      Example in our app:

      db.items.find({ indexedField: ObjectId('523ec53087d4200202000006') })
      completes in ~1 ms with ~400 results, using the indexedField index

      db.items.find({ compoundTextIndex: 'grade' })
      completes in ~180 ms with ~70,000 results, using the compoundTextIndex index

      db.items.find({ indexedField: ObjectId('523ec53087d4200202000006'), compoundTextIndex: 'grade' })
      completes in ~250 ms with ~20 results, using the compoundTextIndex index

      This third example clearly doesn't make sense. It would be faster in this case to perform the indexed query and then filter down the results by text at the application level! It should be using the indexedField index instead of compoundTextIndex.

      This actually crashes our app sometimes, taking over 30 seconds in real-life queries. So it's pretty major for us.

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            cstigler Charlie Stigler
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: