I have database with few fields: _id, field0, field1, field2, field3, field4, field5..

I created index on as follows:

db.usertable.createIndex( { _id: 1, field1:1} )

The expected that a query with projection of those fields in the index, would be fast as all the required data is already in the index. I found that the default index on _id was used instead of the new index I added.

Here is the log line for the query:

2019-05-13T23:37:22.317+0300 I COMMAND  [conn34] command ycsb.usertable command: 
find { find: "usertable", filter: { _id: "user5830762568852303260" }, projection: 
{ field0: 1 }, limit: 1, singleBatch: true, $db: "ycsb", lsid: { id: UUID("372c448a-00e0-4e2e-b68a-651b9f558e3c") }, 
$readPreference: { mode: "primaryPreferred" } } planSummary: IDHACK keysExamined:1 docsExamined:1 cursorExhausted:1 numYields:1 nreturned:1 reslen:257 
locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 2 } }, Collection: { acquireCount: { r: 2 } } } protocol:op_msg 1001ms

My understanding of IDHACK is that once _id is part of the query, the default index on _id is consulted instead of the index I added although it includes all the data for the query.

Does it mean that there's is no point to create any additional index that includes _id as it will be ignored anyway?

Is there a way to fix the index without changing the query so such index will be consulted instead of the default one?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.