0

this is my query for find number of record in one hour

         $rec=0;
         $acst1 = new \MongoDB\BSON\UTCDateTime(strtotime('2017-10-30 21:00:00')*1000);
         $acst2 = new \MongoDB\BSON\UTCDateTime(strtotime('2017-10-30 22:00:00')*1000);

         $collection1 = $db->CR800;
         $filter1 = array(TIMESTAMP=> array('$gte' => $acst1, '$lte' => $acst2));
         $rec= $collection1->count($filter1);

when i execute this query it take 16 sec to execute and when remove greater than less than condition then query executes in just 300ms

there are more than 20 million document in collection

is there a way to optimize this query and reduce execution time ?

NIKHIL PARIHAR
  • 502
  • 6
  • 10
  • Clearly there is no index. Yes a query with no conditions takes no time at all to run through analyzing. With constraints it actually needs to "do something". If you don't have an index that can be optimally looked up, then a database has no option than to run through all the data and test the conditions. This is why you index. – Neil Lunn Oct 30 '17 at 09:24
  • I would generally suggest reading the documentation before you even write another line of code. Here's [all about indexes](https://docs.mongodb.com/manual/indexes/) and if you actually start with [MongoDB CRUD Operations](https://docs.mongodb.com/manual/crud/) it's going to mention ( and reference the documentation for ) indexes several times. – Neil Lunn Oct 30 '17 at 09:34
  • i have to create while creating a collection or i have to create it while fetching data – NIKHIL PARIHAR Oct 30 '17 at 09:35
  • Read please. That is what documentation is for, and I know that everything you could possibly ask is actually clearly stated in it. Welcome to Stack Overflow. Research first, then ask questions about what you have researched if you still don't understand. Not the other way around. – Neil Lunn Oct 30 '17 at 09:37
  • thank you @NeilLunn indexes sugeestion works – NIKHIL PARIHAR Oct 30 '17 at 10:18
  • @NeilLunn as you said i use index it works when i use small time frame but for large time frame in gte lte query still take too much time. – NIKHIL PARIHAR Oct 30 '17 at 14:01

0 Answers0