Usage question - how to get number of records of very large dataset
focussing opened this issue · 4 comments
Hi!
I am using the api to get logging data from a MySQL database. The idea is that the user wants to look at the data "of the last hour" or "last day" etc.
Currently I want to get the last record like this http://192.168.x.xxx/api.php/records/logging?order=date,desc&page=1,1
and that results in the following JSON.
{
"records": [
{
"id": 586355,
"date": "2024-07-15 11:05:35",
"log": "15;0;-99.93,-62.46,-100.04,-100.04,-24.98,-900.11,-29.99,-49.97"
}
],
"results": 586354
}
But it takes around 8 seconds. When I want to retrieve the first record using http://192.168.x.xxx/api.php/records/loggingpage=1,1
the result is presented within one second.
Would there be a faster way, or maybe set it up differently?
Looking forward to hearing from you :)
Best regards
Raymond
Hi Raymond, Did you add an index to the date column? Kind regards, Maurits
Hi Maurits,
Hm, no I did not; I will try that! Thank you!
How would you fetch for example the records of the "last hour"? Just creating a date string from the current time minus one day? Or is there a more fancy way?
Kind regards, Raymond
Just creating a date string from the current time minus one day?
Yes, use DB date strings, such as:
filter=date,gt,2019-03-09 08:20:33
Also known as the MySQL datetime format.