How can I improve the performance of this MySQL query?

Question

I have a MySQL query:

   SELECT DISTINCT 
          c.id, 
          c.company_name, 
          cd.firstname, 
          cd.surname, 
          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?

Answer 1

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.

Answer 2

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.

Answer 3

See: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Especially:

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.)

Answer 4

Hi all, 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. :)