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

Popular posts from this blog

Python - Mathematical Functions - Hexadecimal, Binary, Exponential, Absolute, Round

🐍 Some mathematical functions are in-built in python. Here are some of them. Convert Decimal to Hexadecimal hex(246) Output: '0xf6' hex(512) Output: '0x200' 0x is prefix to all hexadecimal numbers in output. The number after 0x is the hexadecimal value. Convert Decimal to Binary bin(1234) Output: '0b10011010010' bin(246) Output: '0b11110110' bin(512) Output: '0b1000000000' Exponential Function pow() function is similar to num1**num2, but it comes useful when you want to execute (num1**num2)%num3. Passing three arguments to pow(), does this. pow(3,4) Output: 81 This works just like 3**4 pow(3,4,5) Output: 1 But this, works equivalent to (3**4)%5. Either of the two ways can be used. At this point I feel (3**4)%5 is better, as anybody reading the code will understand what is being done immediately. While with pow(2,3,4), the person may have to refer to documentation to understand how this function works. Absolute Value The function abs() is used ...

Two Ways of rendering in React Examples

Two Ways of rendering in React Examples 1 - Render twice HTML < div id = "p1" ></ div > < div id = "p2" ></ div > CSS . person { display : inline - block ; margin : 10px ; border : 1px solid # eee ; box - shadow : 0 2px 2px # ccc ; } JAVASCRIPT (Babel) function Person ( props ) { return ( < div className = ' person "> < h1 >{ props . name }</ h1 > < p > Age :{ props . age }</ p > </ div > ); } ReactDOM . render (< Person name = "VK" age = "29" />, document . querySselector ( '#p1' )); ReactDOM . render (< Person name = "HK" age = "28" />, document . querySselector ( '#p2' )); 2 Render all at once HTML < div id = "app" ></ div > CSS . person { display : inline - block ; margin : 1...

Binary Tree Traversal in Python - In Order, Pre Order, Post Order

Binary Tree Traversal in Python - In Order, Pre Order, Post Order Run the code here to see output:  https://repl.it/@VinitKhandelwal/iterative-traversal-of-binary-tree # Node Class to Create Object of Nodes class Node (): def __init__ ( self , value , left= None , right= None ): self .value = value self .left = left self .right = right # Creation of a Binary Tree class BinaryTree (): def __init__ ( self , mylist ): self .mylist = mylist self .root=Node ( None ) for value in self .mylist : self .curr= self .root self ._add ( value ) def _add ( self , value ): if self .curr.value == None : self .curr.value = value else : if self .curr.value > value : if self .curr.left == None : self .curr.left = Node ( value ) else : self .curr = self .curr.left self ._add ( value ) else : if self...