I'm working on a Spring Boot Project using MongoDB as Database. I'm applying indexing on an embedded object which is slowing down one query but speeding up a similar query.
I have a MongoDB collection having structure somewhat like this:
UserDetails user_details;
String doc_no;
String txn_date;
user_details is an embedded object having below structure:
String user_id;
String org_id;
I have following two queries written in Spring Data MongoDB:
Query query = new Query();
query.addCriteria(Criteria.where("user_details.org_id").is(orgId));
query.with(Sort.by(Sort.Direction.DESC, "txn_date"));
mongoTemplate.find(query,MyClass.class)
with indexing:
mongoTemplate.indexOps("myclass").ensureIndex(new Index().on("user_details.org_id", Sort.Direction.DESC));
And,
Query query = new Query();
query.addCriteria(Criteria.where("user_details.user_id").is(userId).and("doc_no").is(docNo));
query.with(Sort.by(Sort.Direction.DESC, "txn_date"));
mongoTemplate.find(query,MyClass.class)
with indexing:
mongoTemplate.indexOps("myclass").ensureIndex(new Index().on("user_details.user_id", Sort.Direction.DESC));
Now, the problem I am facing is the indexing is making the 2nd query faster than without indexing but in case of 1st query indexing is making it slower than without indexing.
Why? When both the queries and corresponding indexing are almost same, then why the 1st query is getting slower?
If you know, please help me get the explanation or if I'm doing anything wrong.
Indexing keys of "low cardinality" would result slower read than a straight collection scan. In this case the index would be become an additional expense.
The two keys in the embedded document seem to have different cardinality. The user_id should have more cardinality than org_id, this could be the reason while the query on user_id is aided with the index, the index on org_id made an adverse impact.
In general, low cardinality fields are NOT for indexing as it has an adverse impact.
Please see a question & answer on cardinality. Mongodb low cardinality index