Improve query speed

[focussing]

Hello again @mevdschee,

Thank you again for developing this amazing API and dedicating your efforts to it.
Right now we have four machines (portable water purification) running.

For all kinds of logging every machine has a MySQL server and webserver running. Every 5 seconds a log is added.
Monitoring is done through a locally running website after logging on the machine.

At this moment we have around 1.000.000 records in the LOG table. When I want to retrieve the latest logging results I use a query as this:

/api.php/records/log?order=date,desc&page=1,720 (720 is for hone hour of log entries) and this takes around 3 seconds. What I don't understand is why this seems independent of the page size (1000 in this example). The response time for 72, 7200 or 72.000 as page size it is the roughly 3 seconds.
I added an index to the date column already (otherwise response time would be minutes).

Is there a way to speed up response times?

Looking forward to hearing from you.

[mevdschee]

Hi Raymond

Thank you for your message.

Is there a way to speed up response times?

Yes, don't use pagination. Identify the last PK you have and ask for new records or use another field to match (with index) instead of pagination. Pagination requires sequential scanning.

/api.php/records/log?filter=date,ge,2025-02-15&filter=date,lt,2025-02-16

Use this to get all records of today and add hours if needed.

Kind regards,

Maurits

[focussing]

Hi Maurits,

Thank you for your speedy reply :)

This makes a HUGE difference. Now it takes for one hour of log entries around 180ms!

Two questions that I am curious of:

1. Would it also make a difference if my index would be unique?
2. Is there any way to compress the data which is returned? The json data for 2 columns for one week logging data is around 694 KB.

Kind regards,
Raymond

[mevdschee]

Hi Raymond,

Great to hear that your results are good!

1. Would it also make a difference if my index would be unique?

No

2. Is there any way to compress the data which is returned?

Yes, the default solution is to use deflate/gzip on the webserver (nginx or apache) that executes the PHP script and the expected compression ratio is roughly 1:10 (sometimes even better). One could also achieve this from within PHP, but I see no (direct) reason why that would be preferred.

see: https://tecadmin.net/enable-gzip-compression-apache-ubuntu/
also: https://stackoverflow.com/questions/4709076/how-to-enable-gzip

Kind regards,

Maurits