Skip to main content

MongoDB — Indexes — 5

Command for storing data from json file to database

  • mongoimport <filename>.json -d <databasename> -c <collectionname> --jsonArray
  • show dbs
  • use d<dtabasename>
  • show collections

expalin()

  • db.<collection>.explain().find({<field>:{$gt:<num>}})
  • db.<collection>.explain("executionStats").find({<fieldname>:{$gt:<num>}})

Adding index to a field of collection

  • db.<collection>.createIndex({<field>: 1})
    • Ascending
  • db.<collection>.createIndex({<field>: -1})
    • Descending

Remove index of a field of a collection

  • db.<collection>.dropIndex({<field>: <1/-1>})

When a query fetches most of the documents from a collection, then indexing reduces the speed of fetching data. Instead fetching data in a regular fashion will be faster

Compound Index

  • db.<collection>.createIndex({<field1>: <1/-1>, <field2>: <1/-1>})
    • first sorts by field2 and then sorts by field1, both ascending order

Indexes for Sorting

If you sort by an indexed column, MongoDB will use it in sorting query like the following
  • db.<collection>.explain().find({<field>:<value>}).sort({<indexedField>:<1/-1>})

See all Indices in a collection

  • db.<collection>.getIndices()

Creating Unique Index

  • db.<collection>.createIndex({<field>:<1/-1>}, {unique: true})
    • The field to be unique indexed must have all unique values. The operation fails if there are duplicate value for the field

Partial Filters

  • db.<collection>.createIndex({<field>: <1/-1>}, {partialFilterExpression: {<field>: <value>}})
Examples
  • db.contacts.createIndex({age: 1}, {partialFilterExpression: {gender: "male"}})
  • db.contacts.createIndex({age: 1}, {partialFilterExpression: {age: {$gt: 60}}})
If we create partial index as follows:
  • db.contacts.createIndex({age: 1}, {partialFilterExpression: {age: {gender: "male"}}})
Now searching contacts greater than 60
  • db.contacts.find({age: {$gt: 60}})
Let us see the explanation
  • db.contacts.explain().find({age: {$gt: 60}})
    • this query does not use index to search since it s a partial index on gender
The find query will use the index when gender with value male is included in the query
  • db.contacts.find({age: {$gt: 60}, gender: "male"})
Let us see the explanation
  • db.contacts.find({age: {$gt: 60}, gender: "male"})
So partial index creates a small index and makes sure that index is used when searched from the index values and collection scan is done otherwise.
Partial index is used when most queries are for some values only, while we are fine with collection scan when queries involve other values.

Non-existing values is a value in MongoDB. Might create a problem with Unique Index

If you have created a field with unique Index and add more than one documents with no value for that field, the second one that you try to add will give an error.
If you still want to create an index where more than one documents could exist where the value of the field is not there, then you must create a partial filter index as follows:
  • db.<collection>.createIndex({<field>: <1/-1>}, {unique: partialFilterExpression: {<field>: {$exists: true}}})
    • This creates a partial index only for documents where the value for the field exists, so now you can have multiple documents where value does not exist.

Time To Live Index

Add current date to document
  • db.<collection>.insertOne({<dateField>: new Date()})
    • This creates a document with value that looks something like the following: 
ISODate("2012-12-19T06:01:17.171Z")
  • db.<collection>.createIndex({datefield: <1/-1>}, {expireAfterSeconds: <num>})
  • db.<collection>.find().pretty()
    • This will display all historic documents, added before creating index
  • db.<collection>.insertOne({<dateField>: new Date()})
    • Add another document
  • db.<collection>.find().pretty()
    • Running above query after <num> seconds will not display anything as all documents expired
    • On adding new document it reevaluated all documents for the index and that is why the old documents created before index was created gets index condition applied.

Covered Query

When you use index field alone to be retrieved, in that case MongoDB searches in the index only without examining the document, reducing time taken to retrieve documents

Create Index

  • db.<collection>.createIndex({<field>: <1/-1>})

Get back only the field that is indexed

  • db.<collection>.find({<field>: <value>}, {_id: 0, <field>: 1})

Multikey Index

  • db.<collection>.createIndex({<arrayfield>: <1/-1>})
On creating an index on array field creates a multi-key index that stores
no. of documents * no. of values in an array
number of values.
    • Works only with arrays with values directly, not embeded documents
    • Also can be applied to array within an embeded document
  • db.<collection>.createIndex({"<arrayfield>.<subfield>": <1/-1>})
    • Also can be applied to values with embeded documents of an array
Compound Index with Multi-key Index
  • It is possible but only when one among the list of fields to be indexed is multi-key
  • Not possible when more than one field are multi-key

Text Index

  • db.<collection>.createIndex({<textField>: "text"})
    • It creates an array index of all words separated by spaces and removes stop words
    • Can have only one text index per collection

How to search using text index?

  • db.<collection>.find({$text: {$search: "<word>"}})
    • searches for the word
    • by default searches in non-case-sensitive way
  • db.<collection>.find({$text: {$search: "<word1 word2>"}})
    • searches for the two words separately
  • db.<collection>.find({$text: {$search: "\"<word1 word2>\""}})
    • string in quotes searches for the the string as a whole

Drop index by name

  • db.<collection>.getIndexes()
  • db.<collection>.dropIndex(<indexName>)

One text index using multiple fields

  • db.<collection>.createIndex({<textField1>: "text", <textField2>: "text"})
    • This helps to search from both fields
  • db.<collection>.find({$text: {$search: "<word>"}})

Adding Config to Text Index

Text Index to Exclude Words by Language

  • db.<collection>.createIndex({<textField1>: "text", <textField2>: "text"}, {default_language: "english"})
  • db.<collection>.createIndex({<textField1>: "text", <textField2>: "text"}, {default_language: "german"})

Adding weights

  • db.<collection>.createIndex({<textField1>: "text", <textField2>: "text"}, {default_language: "english", weights: {<textField1>: <num>, <textField2>: <num>})
    • Higher the num, higher the weight

Searching from such text index

  • db.<collection>.find({$text: {$search: "<word>", $language: "german"}})
  • db.<collection>.find({$text: {$search: "<word>", $caseSensitive: true}})
  • db.<collection>.find({$text: {$search: "<word>"}}, {score: {$meta : "textScore"}})
    • to get score

Adding index in background

Adding index in foreground locks the collection for the time being. If any other query is executed i  the same time, it will be deferred until the index is created. To avoid that and let the collection be accessible, we can add index in background.
  • db.<collection>.createIndex({<field>: <1/-1>}, {background: true})
    • This lets index be created in the background without locking other operations on the collection. But the index creation takes time in this case.

Comments

Post a Comment

Popular posts from this blog

Python - List - Append, Count, Extend, Index, Insert, Pop, Remove, Reverse, Sort

🐍 Advance List List is widely used and it's functionalities are heavily useful. Append Adds one element at the end of the list. Syntax list1.append(value) Input l1 = [1, 2, 3] l1.append(4) l1 Output [1, 2, 3, 4] append can be used to add any datatype in a list. It can even add list inside list. Caution: Append does not return anything. It just appends the list. Count .count(value) counts the number of occurrences of an element in the list. Syntax list1.count(value) Input l1 = [1, 2, 3, 4, 3] l1.count(3) Output 2 It returns 0 if the value is not found in the list. Extend .count(value) counts the number of occurrences of an element in the list. Syntax list1.extend(list) Input l1 = [1, 2, 3] l1.extend([4, 5]) Output [1, 2, 3, 4, 5] If we use append, entire list will be added to the first list like one element. Extend, i nstead of considering a list as one element, it joins the two lists one after other. Append works in the following way. Input l1 = [1, 2, 3] l1.append([4, 5]) Output...
Post a Comment
Read more

Difference between .exec() and .execPopulate() in Mongoose?

Here I answer what is the difference between .exec() and .execPopulate() in Mongoose? .exec() is used with a query while .execPopulate() is used with a document Syntax for .exec() is as follows: Model.query() . populate ( 'field' ) . exec () // returns promise . then ( function ( document ) { console . log ( document ); }); Syntax for .execPopulate() is as follows: fetchedDocument . populate ( 'field' ) . execPopulate () // returns promise . then ( function ( document ) { console . log ( document ); }); When working with individual document use .execPopulate(), for model query use .exec(). Both returns a promise. One can do without .exec() or .execPopulate() but then has to pass a callback in populate.
1 comment
Read more

683 K Empty Slots

  Approach #1: Insert Into Sorted Structure [Accepted] Intuition Let's add flowers in the order they bloom. When each flower blooms, we check it's neighbors to see if they can satisfy the condition with the current flower. Algorithm We'll maintain  active , a sorted data structure containing every flower that has currently bloomed. When we add a flower to  active , we should check it's lower and higher neighbors. If some neighbor satisfies the condition, we know the condition occurred first on this day. Complexity Analysis Time Complexity (Java):  O(N \log N) O ( N lo g N ) , where  N N  is the length of  flowers . Every insertion and search is  O(\log N) O ( lo g N ) . Time Complexity (Python):  O(N^2) O ( N 2 ) . As above, except  list.insert  is  O(N) O ( N ) . Space Complexity:  O(N) O ( N ) , the size of  active . Approach #2: Min Queue [Accepted] Intuition For each contiguous block ("window") of  k  po...
Post a Comment
Read more