mysql - 我怎样才能提高这个 MySQL 查询的性能？

Question

我有一个 MySQL 查询:

   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

clients 表有大约 60-70k 行，并且有一个“id”、“record_type”、“date_action”和“company_name”的索引——不幸的是，查询仍然需要 5 秒以上才能完成。删除“ORDER BY”将其减少到大约 30 毫秒，因为不需要文件排序。有什么方法可以更改此查询以改进 5 秒以上的响应时间吗？

Answer 1

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

特别是:

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