我有什么选择可以让我的 ORDER BY 更快? [英] What options do I have to make my ORDER BY faster?

问题描述

我有以下查询:

SELECT DISTINCT c.id
FROM clients AS c
LEFT JOIN client_project AS cp ON (cp.client_id = c.id)
WHERE cp.project_id = 1
    AND c.active_flag = 1
ORDER BY c.client_name

如果我删除订单，查询需要 0.005 秒.使用 order by，查询需要 1.8-1.9 秒.我在 client_name 上有一个索引.

If I remove the order by, the query takes 0.005 seconds. With the order by, the query takes 1.8-1.9 seconds. I have an index on client_name.

还有什么可以提高速度?

What else would improve the speed?

c.id 是主键，但在 client_project 中可能有多个记录，因此可能导致每个 id 有多个记录.此外，删除不同的查询会使查询产生 0.1 秒的差异.

c.id is primary key, but there could be multiple records for it in client_project and therefore it may result in more than one record for each id. Also, removing the distinct makes 0.1 second difference in the query.

补充:这是我的客户表:

CREATE TABLE IF NOT EXISTS `clients` (
  `id` int(11) NOT NULL auto_increment,
...
  `organization` varchar(255) character set utf8 collate utf8_bin NOT NULL,
  `client_name` varchar(255) character set utf8 collate utf8_bin NOT NULL,
  `active_flag` tinyint(1) NOT NULL,
...
  PRIMARY KEY  (`id`),
  KEY `active_flag` (`active_flag`),
...
  KEY `organization` (`organization`),
  KEY `client_name` (`client_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

使用 MySQL 5.0

Using MySQL 5.0

推荐答案

尝试将此键添加到 client_projects:

KEY(client_name, id, active_flag)

这篇关于我有什么选择可以让我的 ORDER BY 更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！