How to write query with index intersection with Mongo java driver

Question

I googled and read the official doc of mongodb (http://docs.mongodb.org/manual/core/index-intersection/), but didn't find any tutorial or indications on syntax of query using index intersection.

Does mongodb apply automatically index intersection when the query involves 2 fields which are separately indexed by a single index? I don't think so.

Here is what cursor.explain() show when i run a query between 2 dates and a given "name" ("name" is a field, both date and name are indexed.)

{
  "cursor": "BtreeCursor Name_1",
  "isMultiKey": false,
  "n": 99330,
  "nscannedObjects": 337500,
  "nscanned": 337500,
  "nscannedObjectsAllPlans": 337601,
  "nscannedAllPlans": 337705,
  "scanAndOrder": false,
  "indexOnly": false,
  "nYields": 18451,
  "nChunkSkips":
  "millis": 15430,
  "indexBounds": {
    "Name": [
      [
        "blabla",
        "blabla"
      ]
    ]
  },
  "allPlans": [
    {
      "cursor": "BtreeCursor Name_1",
      "isMultiKey": false,
      "n": 99330,
      "nscannedObjects": 337500,
      "nscanned": 337500,
      "scanAndOrder": false,
      "indexOnly": false,
      "nChunkSkips": 0,
      "indexBounds": {
        "Name": [
          [
            "blabla",
            "blabla"
          ]
        ]
      }
    },
    {
      "cursor": "BtreeCursor Date_1",
      "isMultiKey": false,
      "n": 0,
      "nscannedObjects": 101,
      "nscanned": 102,
      "scanAndOrder": false,
      "indexOnly": false,
      "nChunkSkips": 0,
      "indexBounds": {
        "Date": [
          [
            "2014-08-23 10:28:50.221",
            "2014-08-23 13:28:50.221"
          ]
        ]
      }
    },
    {
      "cursor": "Complex Plan",
      "n": 0,
      "nscannedObjects": 0,
      "nscanned": 103,
      "nChunkSkips": 0
    }

The complex plan shows nothing. And the elapsed time is 16s. If I query only by name without date, it takes only 0.9s

I want to learn how to write query using index intersection in mongojava driver, something like hint() in mongo shell. Any example or tutorial link is welcome.

I know about writing basic queries with Mongodb java driver. You can just post the essential code example if it saves ur time.

Thanks in advance.

Lelouchzqy · Accepted Answer · 2014-09-19 12:41:26Z

4

After reading these links: http://docs.mongodb.org/manual/core/query-plans/#index-filters https://jira.mongodb.org/browse/SERVER-3071

I come to conclude that there is no way for now to force query to use index intersection.

In fact, when several candidate index are possible for a query, mongodb runs them in parallel and waits a index to "win the match". The winner index is the one that completes the whole query first or returns a threshold number of matching result first. Then mongodb uses this index to query.

In the case that your queries are very variant and you cannot build many compound index, its dead. You can only trust mongodb's test.

Sometimes, one index is more selective than another. But it doesn't mean that it returns more quickly the result. Like my case, the "name" index is more selective. It may fetch less documents. But it requires a date comparaison to determine if the fetched document matches the whole query. On the other side, the "date" index fetches more documents from the disque but only does a simple equality test on the "name" field to determine if the document matches the query. That is possibly why it can win the test.

About the index intersection, it has never been used in my several query tests. I doubt if it is useful and expect mongodb to improve its performance in future version.

If my conclusion is wrong, please point it out. Still learning about MongoDB :)

answered Sep 19, 2014 at 12:41

Lelouchzqy

2592 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Howard Lee Over a year ago

I tested running 300 threads on a 100GB datasize with 7 indexes. Each query has two fields, both indexed. In explain(), it says BtreeCursor instead of ComplexPlan, which means index intersection was not used. I ran many queries with same result. I even tested on cold data still same result. Have no idea how to activate index intersection.

Lelouchzqy Over a year ago

@Howard Lee After a little thinking, I come to understand why index intersection is not as rapid as we think. Suppose that we have 2 indexes which have a selectivity of 1/100 for each and we want to execute a query with these 2 indexes for 10000 documents. Using 1 index will reduce the scanning object number to 100, and we do 100 comparisons on the 2nd field to finish the query Using index intersection will raise 2 lists of 100 objects each. And we need to find all _id that coexists in these 2 lists to finish the query. It seems that index intersection is not so rapid :(

Howard Lee Over a year ago

I found some neat commands to verify whether index intersection was considered. Run a .explain(true) will list all index plans, look for complex plan which is index intersection. Another way is in mongo shell, db.getSiblingDB("db").getCollection("collection").getPlanCache().getPlansByQuery({ "field1": "value1", "field2": "value2" }), it will list in detail which index was used and index scores. Most of the time complex plan have a lower score, therefore not considered.

Community · Accepted Answer · 2017-05-23 12:15:26Z

1

Does mongodb apply automatically index intersection when the query involves 2 fields which are separately indexed by a single index?

has been answered here: MongoDB index intersection

You can't force MongoDB to apply index intersections rather you could modify your queries to allow MongoDB query optimizer to apply index intersection strategy on your query.

To learn how your query parameters affect the indexing process, see this link, though it is for compound indexes.

http://java.dzone.com/articles/optimizing-mongodb-compound

And Java API provides two methods to use hint() with the find() operation:

MongoDB Java API

public DBCursor hint(String indexName)

public DBCursor hint(DBObject indexKeys)

Informs the database of indexed fields of the collection in order to improve performance.

which can be used as below,

List obj = collection.find( query ).hint(indexName);

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Sep 18, 2014 at 17:08

BatScream

19.7k4 gold badges54 silver badges68 bronze badges

2 Comments

Lelouchzqy Over a year ago

I read your links. @BatScream Now I know that the index intersection has been considered by MongoDB during the experiment, but finally not used for the query. But I am still confused, and I runned a similar query. If I query only by date range, it returns 2689110 nscanned. If I query only by specfic name, it returns 360330 nscanned. And if I query with the same date range and the same specific name, it use the date index, ah, not the name index which seems to be more selective, not the index intersection which I think is the best. Do you know explain that? I can give explain() if need.Thanks

Lelouchzqy Over a year ago

Another question is how to hint a index intersection if this is possible? Even in mongo shell, i think that we can only hint one index which can be compund. But my queries are a bit variable. I cannot create many compound index. @BatScream

Collectives™ on Stack Overflow

How to write query with index intersection with Mongo java driver

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related