I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is not uncommon for the queue to have several hundred thousand pending items to process.
The selection query is as follows:
SELECT id, user, password, counter, idoc, idoc_type, company_id, host, job_id FROM business_queue WHERE finished IS NULL AND locked='0' AND created <= NOW() AND counter <= 5 AND company_id = 2 ORDER BY business_object_priority DESC, created ASC, id ASC LIMIT 1 FOR UPDATE
The table has the follwing index defined:
ALTER TABLE `business_queue` ADD INDEX compound_selector( `finished` , `locked`, `created`, `counter`, `company_id`, `business_object_priority` ) ;
Yet I have a feeling that it could be massively optimized, as currently filesort is used for sorting and it is kinda inefficient when we have a large pending queue. Is there a way to get rid of filesort in the above query completey?