Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What about defining an index on your columns used in ordering? –  dezso Oct 12 '12 at 9:21
    
Yeah, I have these too, but afaik mysql uses only one index anyway. –  clops Oct 12 '12 at 9:46
    
And adding id to compound_selector? –  dezso Oct 12 '12 at 10:03
    
Sorry, ID is the primary key. According to MySQL Docs the primary key is always automatically added as the very first part of any compound index. –  clops Oct 12 '12 at 11:49
2  
@dezso: I suppose clops is referring to InnoDB, where the clustered key (usually the PK, but not always) is added (as the last part AFAIK) to all non-clustered indices. –  ypercube Oct 12 '12 at 17:01
show 1 more comment

1 Answer

up vote 3 down vote accepted

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 !!!

share|improve this answer
    
Thanks, its awesome! What is the recommended way then to sort a queue to keep up with the following prerequisite: higher priority business objects should be handled first, then order by created date. Filesort is non-avoidable or? –  clops Oct 15 '12 at 7:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.