search

tphakala / birdnet-go

Realtime BirdNET soundscape analyzer
Other
186 stars 19 forks source link

Slow recent detections query as detections increase #241

Closed PeteLawrence closed 3 months ago

PeteLawrence commented 3 months ago

I'm running Birdnet Go on a Raspberry Pi 5 with a SSD, and using MySQL as a backend. I have around 220,000 detections in the records table, and I'm constantly getting the following message in the logs every time I reload the dashboard - 

2024/06/30 11:43:17 /root/src/BirdNET-Go/internal/datastore/interfaces.go:303 SLOW SQL >= 200ms
[962.397ms] [rows:5] SELECT * FROM `notes` ORDER BY date DESC, time DESC LIMIT 5
2024/06/30 11:43:17 Retrieved 5 detections in 962.470349ms

Running EXPLAIN SELECT * FROM notes ORDER BY date DESC, time DESC LIMIT 5 on the database I can see that MySQL is having to do a filesort due to the ordering on 2 different fields resulting in the slowness, and which will only get worse as more detections are added.

I'm not sure whether sqlite suffers from the same problem or not?

I'd suggest that the query needs updating to use a single DATETIME field (eg. begin_time), and an index adding to that field. When doing this the query time goes down to sub 10ms, and should remain pretty constant regardless of the number of detections

tphakala commented 3 months ago

With SQLite I am seeing query times of 3 - 5ms on a rpi5 with sd card, but I do agree that query could be optimized so I changed it to use id column intestad with https://github.com/tphakala/birdnet-go/commit/86a119a36cc0b9835e3c53835c662b1b4af0387a.

Thank you for reporting.

PeteLawrence commented 3 months ago

Many thanks, I've pulled the latest dev image and query times are way down. 👍