Discussion:
Index creation
(too old to reply)
pantonis
2018-10-12 07:10:07 UTC
Permalink
Hi,

I am trying to understand how indexing works on MongoDB. I have a table
Orders and I want to create indexes for ClientId and Timestamp.
I have the following queries:

*where ClientId = 'some client'*

* where Timestamp = 'some time'*
* where ClientId = 'some client' && Timestamp = 'some time'*

and the following indexes:

* collection.Indexes.CreateOne(new
CreateIndexModel<Order>(Builders<Order>.IndexKeys.Ascending("ClientId")));*
* collection.Indexes.CreateOne(new
CreateIndexModel<Order>(Builders<Order>.IndexKeys.Ascending("Timestamp")));*


for running the 3rd query with && do I need the below index or it is
covered by the above two indexes:


*collection.Indexes.CreateOne(Builders<Order>.IndexKeys.Ascending("ClientId").Ascending("Timestamp"));*


Thanks
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/f3ebdb73-4ae2-4817-b3a6-953a162dc880%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
pantonis
2018-10-21 06:28:52 UTC
Permalink
Anyone can help please?
Post by pantonis
Hi,
I am trying to understand how indexing works on MongoDB. I have a table
Orders and I want to create indexes for ClientId and Timestamp.
*where ClientId = 'some client'*
* where Timestamp = 'some time'*
* where ClientId = 'some client' && Timestamp = 'some time'*
* collection.Indexes.CreateOne(new
CreateIndexModel<Order>(Builders<Order>.IndexKeys.Ascending("ClientId")));*
* collection.Indexes.CreateOne(new
CreateIndexModel<Order>(Builders<Order>.IndexKeys.Ascending("Timestamp")));*
for running the 3rd query with && do I need the below index or it is
*collection.Indexes.CreateOne(Builders<Order>.IndexKeys.Ascending("ClientId").Ascending("Timestamp"));*
Thanks
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/75511710-28e6-49d3-beac-078c4c00e3cf%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
'Kevin Adistambha' via mongodb-user
2018-10-22 05:15:50 UTC
Permalink
Hi,

I believe you’re trying to create indexes using the MongoDB dotnet driver.
Is this correct?

From what I understand, you’re trying to do queries similar to:

db.collection.find({ClientId: ...})
db.collection.find({Timestamp: ...})
db.collection.find({ClientId: ..., Timestamp: ...})

and the index you’re building are:

db.collection.createIndex({ClientId: 1})
db.collection.createIndex({Timestamp: 1})

If this is accurate, strictly from the assumptions I made, then it’s likely
that the two indexes won’t be able to help with the third query ({ClientId:
..., Timestamp: ...}) since MongoDB’s find() query typically use only one
index. You would need to create a compound index
<https://docs.mongodb.com/manual/core/index-compound/> e.g. db.collection.createIndex({ClientId:
1, Timestamp: 1}) to cater for the third query. This compound index will
also help with the first two queries, *as long as you don’t do any sorting
or range queries such as using $gte or similar*.

For more details on compound indexes, please see:

- Compound indexes <https://docs.mongodb.com/manual/core/index-compound/>
- Create Indexes to Support Your Queries
<https://docs.mongodb.com/manual/tutorial/create-indexes-to-support-queries/>
- Optimizing MongoDB Compound Indexes
<https://emptysqua.re/blog/optimizing-mongodb-compound-indexes/>

Having said that, to discover the effectiveness of your indexes, you should
use the db.collection.explain().find(...) method in the mongo shell. The
output of this is described in detail in:

- Explain Results
<https://docs.mongodb.com/manual/reference/explain-results/>
- Analyze Query Performance
<https://docs.mongodb.com/manual/tutorial/analyze-query-plan/>

If this is not the case, could you post an example document and the example
query you’re tyring to do, along with your MongoDB version?

Best regards,
Kevin
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/018806ab-3789-4f2a-b16e-8a70f4e026d8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
pantonis
2018-10-24 07:08:31 UTC
Permalink
Hi Kevin,

*I believe you’re trying to create indexes using the MongoDB dotnet driver.
Is this correct?*
*Correct*

*If this is accurate, strictly from the assumptions I made, then it’s
likely that the two indexes won’t be able to help with the third query
({ClientId: ..., Timestamp: ...}) since MongoDB’s find() query typically
use only one index. You would need to create a compound index
<https://docs.mongodb.com/manual/core/index-compound/> e.g. db.collection.createIndex({ClientId:
1, Timestamp: 1}) to cater for the third query. This compound index will
also help with the first two queries, as long as you don’t do any sorting
or range queries such as using $gte or similar.*

*So if I use the compound index as you said it will help with all 3
queries. That is excellent. The problem I see here is that I do sorting and
range queries so based on what you described this will be a problem. May I
ask what is your suggestion so that I will be able to use sorting and
ranging efficiently?*

Thanks again.
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/0d82bd10-494d-4144-aea7-4a300ead3bb9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Sandeep Singh
2018-10-24 10:09:15 UTC
Permalink
Hi,
As suggested by Kevin, a compound will cater to all three queries unless
you do some range or sort queries. This compound index will take care of
below queries:
*Index:*
db.collection.createIndex({ClientId: 1, Timestamp: 1})

*Queries:*
db.collection.find({ClientId: ...}) db.collection.find({Timestamp: ...})
db.collection.find({ClientId: ..., Timestamp: ...})
db.collection.find({ClientId: ...}).sort({Timestamp:1})
db.collection.find({ClientId: ...}).sort({Timestamp:-1})
db.collection.find({Timestamp: {$gt: SomeDateTime}}).sort({ClientId:1})
Post by pantonis
Hi Kevin,
*I believe you’re trying to create indexes using the MongoDB dotnet
driver. Is this correct?*
*Correct*
*If this is accurate, strictly from the assumptions I made, then it’s
likely that the two indexes won’t be able to help with the third query
({ClientId: ..., Timestamp: ...}) since MongoDB’s find() query typically
use only one index. You would need to create a compound index
1, Timestamp: 1}) to cater for the third query. This compound index will
also help with the first two queries, as long as you don’t do any sorting
or range queries such as using $gte or similar.*
*So if I use the compound index as you said it will help with all 3
queries. That is excellent. The problem I see here is that I do sorting and
range queries so based on what you described this will be a problem. May I
ask what is your suggestion so that I will be able to use sorting and
ranging efficiently?*
Thanks again.
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups
"mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit
https://groups.google.com/d/msgid/mongodb-user/0d82bd10-494d-4144-aea7-4a300ead3bb9%40googlegroups.com
<https://groups.google.com/d/msgid/mongodb-user/0d82bd10-494d-4144-aea7-4a300ead3bb9%40googlegroups.com?utm_medium=email&utm_source=footer>
.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAAjZhnCE4mNVVq9UFv0Mxijg56oQSV_%2BXUWmJRGfZqegT5fMiQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
'Kevin Adistambha' via mongodb-user
2018-10-25 05:05:16 UTC
Permalink
Hi Sandeep,

Great pointers. However may I point out that the query:

db.collection.find({Timestamp: ...})

will not use the index {ClientId: 1, Timestamp: 1}, since the field
Timestamp is not a prefix of the index
<https://docs.mongodb.com/manual/core/index-compound/#compound-index-prefix>.
All the other queries should use the index as expected.

Pantonis, in order to determine the right index, please post the queries
you’re planning to do. A good rule of thumb are:

- Don’t create too many indexes that look similar. It’s best to create a
small number of indexes that can cater to as many queries as possible.
- Use the explain results
<https://docs.mongodb.com/manual/reference/explain-results/index.html>
and see if any stage contains COLLSCAN. This is a collection scan, which
means that to answer the query, MongoDB must go through the whole
collection, which is expensive. IXSCAN means that MongoDB can use an
index for the query, and typically is much faster.

Indexing is a deep subject, and I would recommend you to peruse the links I
posted in my earlier post to have a thorough understanding of how indexing
works in MongoDB.

Best regards,
Kevin
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/7a55c87f-083e-4ae2-8d4f-5ad479b2f6cb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Continue reading on narkive:
Loading...