very slow dequeue on large number of jobs

[ghost]

• Minion version: 10.21
• Perl version: 5.28.1
• Operating system: Debian Buster 10

Steps to reproduce the behavior

Minion dequeue on a mysql backend slows down significantly when large number of jobs is present

Expected behavior

dequeue should have more linear dependency on the size of minion_jobs table

Proposed changes

i think i have tracked the issue to the query in

    UPDATE minion_jobs job
    SET job.started = NOW(), job.state = 'active', job.worker = ?,
        job.id = \@dequeued_job_id := job.id
    WHERE job.state = 'inactive' AND job.`delayed` <= NOW()
      AND NOT EXISTS (
        SELECT 1 FROM minion_jobs_depends depends
        LEFT JOIN (
          SELECT id, state, expires
          FROM minion_jobs
        ) AS parent ON parent.id=depends.parent_id
        WHERE child_id=job.id
          AND (
            parent.state = 'active'
            OR ( parent.state = 'failed' and not job.lax )
            OR ( parent.state = 'inactive' and (parent.expires is null or parent.expires > now()))
        )
      )
      AND job.id = COALESCE(?, job.id) AND job.queue IN ($qq) AND job.task IN ($qt)
      AND (expires is null or expires > now())
    ORDER BY job.priority DESC, job.created
    LIMIT 1

which could take multiple seconds on large job collections, on a fast-moving job queue this sometimes makes a difference, cpu utilization in my tests went from 100% to as low as 10% on ca. 100k pending jobs, didn't test any further.

Replacing the index that is being created with create index minion_jobs_state_idx on minion_jobs (state); with another index type create index minion_jobs_state_idx on minion_jobs (state, priority DESC, created); seems to resolve the slow dequeue problem.