CODEWITHSUNDEEP
mongodbindexing
RockScience
RockScience

Reputation: 18600

use indexing to speed up reading from mongodb database

I have a collection of documents that is very very large (hundreds of million documents)

each document is as such

There are ~50 different names, ~60 different type1, ~20 different type2

I need to read from this database, usually it is either:

  1. a unique set of (name,type1,type2), but with all dates
  2. a few dates for all type1
  3. List item

Currently I am reading without any indexing and it is very slow! Much slower than having a few flat sql tables...

How can I use index to speed up this database?

thanks

Upvotes: 0

Views: 138

Answers (1)

mnemosyn
mnemosyn

Reputation: 46311

date in YYYY/MM/DD format

There's MongoDB datetime format. Use it. It uses less memory than the string and it doesn't need additional conventions. Your format is sane in the sense that it's lexicographical ordering is equivalent to chronological ordering for dates between 0001-01-01 and 9999-12-31, but the built-in datatype is definitely preferable for range queries.

There are ~50 different names, ~60 different type1, ~20 different type2

Your keys have very low selectivity, so individual indexes are probably pointless

I need to read from this database, usually it is either:

  • a unique set of (name,type1,type2), but with all dates

Use a compound index for {name, type1, type2}. If you also need chronological ordering, you might want to add date for sorting or use a monotic primary key like ObjectId and rely on natural ordering.

db.collection.ensureIndex({'name' : 1, 'type1' : 1, 'type2' : 1, 'date' : 1});
  • a few dates for all type1

What is 'a few dates'? I assume you mean something like "all dates in a given date range"? Use an index for date. date should naturally have a much better selectivity, so individual keys make sense.

db.collection.ensureIndex({'date' : 1});
  • List item

List all items? Any ordering? You need something more specific. Keep in mind that skip/take is expensive.

Upvotes: 1

Related Questions