Not sure if it makes a big difference to the answer, but MySQL was a typo - the edge db on the Rpi is Sqlite3.
On Thu, Jun 19, 2025 at 5:00 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > On Thu, 2025-06-19 at 16:50 +1200, Al Grant wrote: > > I want to create a web based dashboard for all the currently detected > > signals, where the dashboard contains a graph of the daily beep rate > > for each channel (max 100 channels) over user selectable periods from > > 1 week to 1 year - that query does not scale well if I query the bpm > > table. > > > > To avoid this I have created a bpm summary table which is generated > > periodically (hourly) off the bpm table. The bpm summary table > > contains the dominant beep rate for a given hour (so 2 records per day > > per channel assuming a signal is detected). > > > > Does this summary table approach make sense? > > Yes. Pre-aggregation to reduce the data volume is common in a > data warehouse. Ideally, you don't have to scan the entire base > table to create the summary. You can consider a trigger for that, > but more elegant might be a solution based on partitioning, where > you aggregate a partition once it is complete. > > > I have noted that I am periodically syncing from MySQL to the server, > > and then periodically updating the summary table - its multi stage > > syncing and I wonder if that makes this approach fragile (although I > > don't see any alternative). > > "MySQL" and "fragile" in the same sentence. Sounds about right. > > Yours, > Laurenz Albe -- "Beat it punk!" - Clint Eastwood
