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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

        finished IS NULL
    AND locked='0'
    AND created <= NOW()
    AND counter <= 5
    AND company_id = 2
    business_object_priority DESC, created ASC, id ASC

The table has the follwing index defined:

ALTER TABLE  `business_queue` ADD INDEX compound_selector(
   `finished` ,  
   `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
@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
up vote 3 down vote accepted

Let's look at the WHERE clause

        finished IS NULL
    AND locked='0'
    AND created <= NOW()
    AND counter <= 5
    AND company_id = 2
    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 (
    `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

Your Answer


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.