kostikov@zotum.net

@Mario Vavti
Without `worker_id`, since omitting `worker_reservationid` did not have a positive effect.
ALTER TABLE workerq ADD INDEX idx_claim (workerq_reservationid, workerq_priority DESC);

@Mario Vavti Then you're in luck :rolling_on_the_floor_laughing:

@Mario Vavti
Yes, it seems everything works fine even without `worker_id`. Perhaps, to ensure compatibility with older versions of MySQL, you could also omit `DESC`.

@Mario Vavti No, but we can give a try.

@Mario Vavti That's right. But the queue moves along normally after the index is added.

@Mario Vavti
Here is how EXPLAIN looked like before the index have been added

hz@localhost [hz]> EXPLAIN ANALYZE
    -> SELECT workerq_id, workerq_cmd FROM workerq
    -> WHERE workerq_reservationid IS NULL
    -> ORDER BY workerq_priority DESC, workerq_id ASC
    -> LIMIT 1 FOR UPDATE SKIP LOCKED;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 1 row(s)  (cost=401951 rows=1) (actual time=37560..37560 rows=1 loops=1)
    -> Sort: workerq.workerq_priority DESC, workerq.workerq_id, limit input to 1 row(s) per chunk  (cost=401951 rows=3.12e+6) (actual time=37560..37560 rows=1 loops=1)
        -> Index lookup on workerq using workerq_reservationid (workerq_reservationid=NULL), with index condition: (workerq.workerq_reservationid is null)  (cost=401951 rows=3.12e+6) (actual time=0.245..34819 rows=6.37e+6 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (37.63 sec)

@Mario Vavti
It was this one on the queue with 6M+ tasks
SELECT workerq_id, workerq_cmd FROM workerq WHERE workerq_reservationid IS NULL ORDER BY workerq_priority DESC, workerq_id ASC LIMIT 1 FOR UPDATE SKIP LOCKED;
The MySQL version is 8.0.46

@Hubzilla Support Forum
If you are under reliatively fresh MySQL 8 then try to add this index
ALTER TABLE workerq ADD INDEX idx_claim (workerq_reservationid, workerq_priority DESC, workerq_id);
In my case, on the `dev` branch of Hubzilla, this significantly improved the performance of the queueworker from tens of seconds to a couple hundred milliseconds.
UPD: CPU usage has also dropped sharply.

FYI @Mario Vavti

@Hubzilla Support Forum
It looks like AI-generated nonsense.