Index strategy for a Queue Table, getting rid of filesort

Question

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?

Answer 1

Let's look at the WHERE clause

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

Since company_id and locked are static values, they should be the leading columns of the index. Your ORDER BY has business_object_priority DESC and created ASC. Because of this mixed order, there will always be a filesort to contend with.

Notwithstanding, I would rearrange the column order as follows

ALTER TABLE  `business_queue` ADD INDEX compound_selector_new (
    `company_id`,   
    `locked`,    
    `finished`
    `business_object_priority`) ;

Notice the following:

• I removed created and counter because you have ranges specified in the WHERE clause.
• I kept finished because you need all records with company_id=2 and locked=0 grouped with all finished columns that are NULL.

Although there will be a filesort, this should yield a faster collection of rows.

Give it a Try !!!