Take the 2-minute tour ×

I have a MySQL query:

          cis.description AS industry_sector 
     FROM (clients c) 
     JOIN clients_details cd ON c.id = cd.client_id 
LEFT JOIN clients_industry_sectors cis ON cd.industry_sector_id = cis.id 
    WHERE c.record_type='virgin'
 ORDER BY date_action, company_name asc, id desc 
    LIMIT 30

The clients table has about 60-70k rows and has an index for 'id', 'record_type', 'date_action' and 'company_name' - unfortunately the query still takes 5+ secs to complete. Removing the 'ORDER BY' reduces this to about 30ms since a filesort is not required. Is there any way I can alter this query to improve upon the 5+ sec response time?

share|improve this question
Run an EXPLAIN on the query. It will show you all the steps to complete and how many rows it has to process. If you can post that I can probably give you a better answer. –  Cfreak Oct 15 '10 at 15:24
Indexes -- are they separate, or a single, covering index? –  OMG Ponies Oct 15 '10 at 15:25
Hi @Cfreak, I've run explain on the query and I get: 1 SIMPLE c range PRIMARY,record_type record_type 2 NULL 67836 Using where; Using temporary; Using filesort 1 SIMPLE cd ref client_id client_id 4 ihasco_live.c.id 1 1 SIMPLE cis eq_ref PRIMARY PRIMARY 4 ihasco_live.cd.industry_sector_id 1 –  Nathan Pitman Oct 16 '10 at 23:02
@OMG Ponies: The indexes are all separate. :) –  Nathan Pitman Oct 16 '10 at 23:03

4 Answers 4

up vote 2 down vote accepted



In some cases, MySQL cannot use indexes to resolve the ORDER BY (..). These cases include the following:
You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)

share|improve this answer
I've changed my approach in light of the above, thanks for the pointer. :) –  Nathan Pitman Oct 19 '10 at 10:34

You have an index for id, record_type, date_action. But if you want to order by date_action, you really need an index that has date_action as the first field in the index, preferably matching the exact fields in the order by. Otherwise yes, it will be a slow query.

share|improve this answer

Without seeing all your tables and indexes, it's hard to tell. When asking a question about speeding up a query, the query is just part of the equation.

  • Does clients have an index on id?
  • Does clients have an index on record_type
  • Does clients_details have an index on client_id?
  • Does clients_industry_sectors have an index on id?

These are the minimum you need for this query to have any chance of working quickly.

share|improve this answer
Hi Andy, yes clients has an index on id and record type and clients_details has an index on client_id, clients_industry_sectors also has an index on id. :) –  Nathan Pitman Oct 16 '10 at 23:04

thanks so much for the input and suggestions. In the end I've decided to create a new DB table which has the sole purpose of existing to return results for this purpose so no joins are required, I just update the table when records are added or deleted to/from the master clients table. Not ideal from a data storage point of view but it solves the problem and means I'm getting results fantastically fast. :)

share|improve this answer

Your Answer


