How to get result faster with MongoDB queries and index
I am running a Rails App and using mongoid. I have created Account as a
Mongoid::Document in my rails app.
There is large amount of records in account document, I have added
compound index to fast the queries. but the performance of the application
is poor.
I tried to do explain on query the, but the indexOnly attribute is showing
as false value.
Can anyone please let me know what is the best way to create a compound
index.
How can I check that the query I have written is using the index properly?
Here is my Query
first_record = Account.where(column_1: 5, column_2: "xxxx", column_3:
"xxxxxx", column_4: "xxxxxxxxxxx", column_5: "xxxxxxxxx")
first_record.explain() => {"cursor"=>"BtreeCursor provider_5_params_idx",
"isMultiKey"=>false, "n"=>10320, "nscannedObjects"=>10320,
"nscanned"=>10320, "nscannedObjectsAllPlans"=>10320,
"nscannedAllPlans"=>10320, "scanAndOrder"=>false, "indexOnly"=>false,
"nYields"=>317, "nChunkSkips"=>0, "millis"=>222464,
"indexBounds"=>{"column_1"=>[[5, 5]], "column_2"=>[["xxxx", "xxxx"]],
"column_3"=>[["xxxxx", "xxxxx"]], "column_4"=>[["pxxxx", "xxxxxxx"]],
"column_5"=>[["xxxxxx", "xxxxxx"]]}, "allPlans"=>[{"cursor"=>"BtreeCursor
provider_5_params_idx", "n"=>10320, "nscannedObjects"=>10320,
"nscanned"=>10320, "indexBounds"=>{"column_1"=>[[5, 5]],
"column_2"=>[["xxxx", "xxxx"]], "column_3"=>[["xxxx", "xxxx"]],
"column_4"=>[["xxxx", "xxxxx"]], "column_5"=>[["xxxxx", "xxxxx"]]}}],
"oldPlan"=>{"cursor"=>"BtreeCursor provider_5_params_idx",
"indexBounds"=>{"column_1"=>[[5, 5]], "column_2"=>[["xxxx", "xxxx"]],
"column_3"=>[["xxxxx", "xxxxx"]], "column_4"=>[["xxxx", "xxxxx"]],
"column_5"=>[["xxxxx", "xxxxx"]]}}, "server"=>"xxxxxxxxx"}
Also I have created index with the following way.
db.account.ensureIndex({column_1:1,column_2:1,column_3:1,column_4:1,column_5:1},
{name:"provider_5_params_idx",background:true});
No comments:
Post a Comment