I have a mongo collection. My collection can be considered like this:
{
"kwId" : "123445",
"p1": "15"
"p2": "25",
"date": "2017-01-28"
}
{
"kwId" : "123446",
"p1": "25"
"p2": "30",
"date": "2017-01-28"
}
{
"kwId" : "123445",
"p1": "35"
"p2": "40",
"date": "2017-01-27"
}
{
"kwId" : "123446",
"p1": "30"
"p2": "50",
"date": "2017-01-27"
}
For each date, I have around 44k documents. Let's say, I want to find differences of p1 and p2 values for documents having same kwId but different date such as 2017-01-28 and 2017-01-27. Example result:
{
"kwId" : "123445",
"p1": "20"
"p2": "15",
}
{
"kwId" : "123446",
"p1": "5"
"p2": "20",
}
What is the most efficient way to do that? I'm using PHP and what I've tried is, retrieve all the documents for given dates and calculate the difference in PHP. It was pretty slow.
I realized that the right way is to make the calculations with PHP. First, I've changed the structure. I made both kwId and date the id. The are 2 reasons for that.
I'm querying the documents by both kwId and date. So they must be indexed to speed up the process.
{ "id": { "kwId": 123445, "date": "2017-01-28" }, "p1": "15", "p2": "25" }
In my question I said "I've tried is, retrieve all the documents for given dates and calculate the difference in PHP. It was pretty slow.". It was taking around 50 minutes for process to be completed. After little change in the document structure as I mentioned above, and using binary search instead of php's default search function, now I have to wait just for milliseconds.