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});
Is it a typo that you index column_1 twice?
The fact that 10320 objects are scanned, similar to plan, is the index is not working.