Hello

We have a trigger with many events. In our default Dashboad we have the "Problems" widget (see image). This widget generates a very slow query when this trigger is displayed (more than 1 min of execution time).

This problem occurs only with this trigger that has many events.

The slow query is

SELECT DISTINCT e.eventid,e.clock,e.ns,e.objectid,er1.r_eventid
FROM events e LEFT JOIN event_recovery er1 ON er1.eventid=e.eventid
WHERE e.source='0' AND e.object='0' AND e.objectid='580090' AND e.eventid<='280382509' AND e.value='1'
ORDER BY e.eventid DESC LIMIT 20

The query plan

For another trigger with less events the plan is

As you can see, Mysql does a full scan on events table when the trigger has many events.

mysql> select count(1)
-> FROM
-> events e LEFT JOIN event_recovery er1 ON
-> er1.eventid=e.eventid
-> WHERE
-> e.source='0' AND
-> e.object='0' AND
-> e.objectid='580090' AND
-> e.eventid<='280395347' AND
-> e.value='1';

1 row in set (0.45 sec)

The problem is related to the query limit. The same query without the limit is fast even for this trigger with many events.

Query plan without limit

See

https://www.percona.com/blog/2006/09/01/mysql-order-by-limit-performance-optimization/

This problem only occurs in this widget. In the event details screen, the search is fast. The query for the event details screen changes a bit:

SELECT DISTINCT e.eventid,e.source,e.object,e.objectid,e.clock,e.ns,e.acknowledged,er1.r_eventid
FROM events e LEFT JOIN event_recovery er1 ON er1.eventid=e.eventid
WHERE e.source='0' AND e.object='0' AND e.objectid='580090' AND e.eventid<='280392998' AND e.value='1'
ORDER BY e.clock DESC,e.eventid DESC LIMIT 20

Thank you