Possible performance issues in the database driver

Version: 2.3.5

Hello,

during the review of the code I encountered this issue, that could be a problem for someone with a lot of jobs in the queue: the yii\queue\db\Queue driver has these two queries:

andWhere('[[pushed_at]] <= :time - [[delay]]', [':time' => time()]

'[[reserved_at]] is not null and [[reserved_at]] < :time - [[ttr]] and [[done_at]] is null',

Problem with these two is that there is an operation on the rights side, that is dependent on a value from the record. This makes the index on those columns pointless, because the database has to examine all records that are not filtered out by other filters in the query.

Please be advised that this comment is wrong in the code: "reserved_at IS NOT NULL forces db to use index on column, otherwise a full scan of the table will be performed" => actually what this does is that the index will only be used to get to all of the records that have a not null reserved_at value, but will have to scan after this all those. If they are scattered over a large table then this can be sometimes even slower than a full table scan. So if one has lots of records with a not null reserved_at value, then the query will still be slow.

I would recommend refactoring the driver to use a reserved_until value instead of a reserved_at. By setting an explicit timestamp when a job can be considered for execution these queries would stay quick even with millions of records in a table.

I understand that this is a breaking change for some, while not an issue for the most of the users. Please consider this request in future versions.

Thank you.

Start in the yii\queue\db\Queue driver and inspect the two database queries involving pushed_at, reserved_at, delay, and ttr. Compare their behavior on a large table, then determine the compatibility and schema implications of introducing reserved_until; done means an agreed refactor with the performance concern validated.