Hi everyone,
I've been troubleshooting some significant performance bottlenecks with the Alarm Journal component (Perspective) using a MySQL backend. We have a large dataset, and querying historical alarms was taking an excessive amount of time, sometimes leading to timeouts.
After deep-diving into the MySQL slow query logs and analyzing the execution plans, I noticed the generated query structure seems suboptimal.
Here is the query generated by the component:
SELECT d.id, d.propname, d.dtype, d.intvalue, d.floatvalue, d.strvalue
FROM alarm_event_data d
LEFT JOIN alarm_events e ON d.id = e.id
WHERE e.eventtime >= '2026-01-28 15:36:51.309'
AND e.eventtime <= '2026-02-18 15:36:51.309'
AND ((e.priority = 1 OR e.priority = 2 OR e.priority = 3 OR e.priority = 4)
AND e.eventflags & 1 = 0);
The issue:
The query performs a "FROM alarm_event_data d LEFT JOIN alarm_events e". However, all the filtering logic (timestamp range, priority, flags) is applied to the "alarm_events" table (the right side of the join).
Since we are filtering by "eventtime", wouldn't it be much more efficient for the query to be structured as "FROM alarm_events JOIN alarm_event_data"? By starting with "alarm_event_data" (which holds the raw values and has significantly more rows), the database optimizer seems to struggle to use the indexes on "alarm_events` effectively, occasionally forcing full table scans or inefficient nested loops.
Is there a way to force the Alarm Journal to optimize this join order?
Has anyone else experienced this specific behavior with MySQL and found a workaround (besides throwing massive amounts of RAM/IOPS at the DB)?
Thank you very much!