6. MongoDB : Work with Index

6. MongoDB : Work with Index

*******************************************************************************
db.items.createIndex({..  ,  ..}, {.....}
                                                 {name:"myOwnIndex_name"}
                                                 {unique:true}
                                                 {default_languate:"spanish"}
                                                 {background:true}

db.items.dropIndex({...}
db.items.find({....}).hint({item:1})
db.items.find({....}).hint({$natural:1})
*******************************************************************************

############################################################### COLLECTION & DOCUMENT###############################################################

user test;

db.createCollection("items"); //create collection, collection or table name is "items" here

//it contains embedded document (details)
db.items.insert({"item":"Book","available":true,"soldQty":144821,"category":"NoSQl","details":{"ISDN":"1234","publisher":"XYZ Company"},"onlineSale":true});

db.items.find({"item":"Book"});
db.items.find({"item":"Book"}).pretty();

############################################################### INDEX###############################################################

create Normal Index.#####################################################

db.items.createIndex({"item":1}); //accending

db.items.createIndex({"item":-1}); //decending

db.items.getIndexes(); //list out all indexes

db.items.dropIndexes();
db.items.dropIndex("item");

//INDEX ON EMBEDDED DOCUMENT ###########################################

db.items.createIndex({"details.ISDN":1})

COMPOUND INDEX #################################################

db.items.createIndex({"item":1,"available":-1});
//index name = item_1_available_1

COMPOUND INDEX with ARRAY AND FIELD##############################
//here courses is array, and name is field
//creating index on array -> MangoDB - creates index on each element in array, so we should be very care full while creating

db.items.createIndex({"courses":1,"name":-1})

HASHED INDEX#############################################

db.items.createIndex({item:"hashed"})

unique INDEX #################################################
//unique ness on value inside document.

db.items.createIndex({"item":1},{unique:true});

TEXT INDEX#############################################
//we can give our own name for index
db.items.createIndex({item:"text"},{name:"item_index"})

TEXT INDEX - default language ##############################

db.customer_info.createIndex({"item":"text"},{default_languate:"spanish"})

INDEX CREATING IN BACKGROUND ##############################
//key like "item" need not to be in  " ".... (Its not mandatory)

db.items.createIndex({item:1},{background:true});

DROP INDEX ######################################

db.items.dropIndex({"item":1})
db.items.dropIndexes()

MONITOR INDEX GOING STATUS AND KILL ##############################

db.currentOp() //to monitor index going status
db.killOp()

PRINT ALL INDEXES  FROM ALL COLLECTIONS ##############################
//Note: we have db.items.getIndexes() -> which gets all index for particular colletion
//to get all indexes from all collection we have to for loop and print as shown below.

db.getCollectionNames().forEach(
function(collection)
   {
      indexes = db[collection].getIndexes();
      print("indexes for collection :"+collection+" : ");
      printjson(indexes);
    }
)

TO CHECK THE SQL QUERY USED FOR INDEX CREATION##############################

db.collection.explain() OR  cursor.explain()

FEW HELP QUIRES ##############################

help

db.items.help()

db.items.storageSize()
db.items.totalIndexSize()
db.items.reIndex()
db.printCollectionStats()

FIND WITH HINT COMMAND ##############################
//this is to tell mangodb to use only index mentioned in the hint while finding the records

//here to use "item" index using hint
db.items.find({item: "Book", available: true }).hint({item:1})

//here not to use any index - using $natural
db.items.find({item: "Book", available: true }).hint({$natural:1}).explain("executionStats")