findFinalizedCommands(timestamp, limit) query

[botnumberseven]

I can see the query below is ran over and over against opertionalDB, like every 30sec or more often.

SELECT id, name, data, sequence, ready_at AS readyAt, delay, started_at AS startedAt, deadline_at AS deadlineAt, period, status, message, parent_id AS parentId, transactional, retries, created_at AS createdAt, updated_at AS updatedAt FROM commands AS commands WHERE commands.status IN ('COMPLETED', 'FAILED', 'EXPIRED', 'UNKNOWN') AND commands.started_at <= 1728835786885 ORDER BY startedAt ASC LIMIT 1000

I believe it is a result of this function - async findFinalizedCommands(timestamp, limit)

My commands table is ~2M rows. So every time query is ran thru Full Table Scan. On 4 Cores, 8GB RAM VPS (4GB innodb buffer) with 2M rows in commands table it takes ~20 seconds to run. And it does it over and over again, non stop.

I'm not clear on the actual goal of this query/function, but it begs for some change/optimization, as when commands table becomes big, table scan becomes expensive. It does not prevent node from running though.

[Mihajlo-Pavlovic]

Thanks for reporting, we planned to add indexes for v8 updates