我有什么选择让我的ORDER BY更快？

I have the following query:

我有以下查询:

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

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.

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

What else would improve the speed?

还有什么可以提高速度?

Edit: 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.

编辑:c.id是主键,但在client_project中可能有多条记录,因此每个id可能会产生多条记录。此外,删除distinct在查询中产生0.1秒的差异。

Addition: Here is my clients table:

另外:这是我的客户表:

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;

Using MySQL 5.0

使用MySQL 5.0

9 个解决方案

#1

looking at your edits

看着你的编辑

try using EXISTS in that case

在这种情况下尝试使用EXISTS

SELECT  c.id
FROM clients AS c
WHERE EXISTS (SELECT * FROM  client_project AS cp  
              WHERE cp.client_id = c.id and cp.project_id = 1)
AND c.active_flag = 1

#2

Try adding this key to client_projects:

尝试将此密钥添加到client_projects:

KEY(client_name, id, active_flag)

#3

Probably there are indices on clients.id and clients.active_flag, so there is no need for the optimizer to go to the full table (or the additional index) unless you want to sort on it.

可能在clients.id和clients.active_flag上有索引,因此除非你想对它进行排序,否则优化器不需要转到完整表(或附加索引)。

Check the optimizer plan, I think in mySQL it is explain .

检查优化器计划,我认为在mySQL中是解释。

An index on client_name, id may help (or it may not - check the plan).

client_name上的索引,id可能有帮助(或者可能没有 - 检查计划)。

A couple of additonal questions/ideas/remarks that may help ...

一些可能有帮助的附加问题/想法/评论......

• Why order by name if all you get from the select is the id

如果从select中获得的是id,为什么按名称排序

• Why do a left join if you have a where clause of "cp.project_id", so clients without a project won't be returned anyways

如果你有一个where子句“cp.project_id”,为什么左连接,所以没有项目的客户端不会被返回

• As to the other posters (paul, eppz), "distinct" may be needed for clients with more than one project. So another idea would be to do something like

  至于其他海报(paul,eppz),对于有多个项目的客户可能需要“不同”。所以另一个想法是做类似的事情

  select id from clients c where exists (select * from client_project cp where c.id = cp.client_id)

  从客户端c中选择存在的id(从client_project cp中选择*,其中c.id = cp.client_id)

#4

I don't have a solution for you, but I do have an explanation.

我没有给你解决方案,但我确实有解释。

MySQL only uses a single index per table. You have two tables, and the indexes used in those are the Primary Key of one (WHERE cp.project_id = 1) and the join is forcing the use of the second table index to efficiently join.

MySQL每个表只使用一个索引。您有两个表,其中使用的索引是一个主键(WHERE cp.project_id = 1),并且连接强制使用第二个表索引来有效地连接。

Using ORDER BY after that it therefore becomes impossible for MySQL to use an index to order. Adding more indexes will not help. EXPLAIN will show you which indexes MySQL has chosen to use for each table. Forcing an index will cause the other parts of the query to slow down.

因此,在此之后使用ORDER BY,MySQL无法使用索引进行排序。添加更多索引无济于事。 EXPLAIN将显示MySQL选择用于每个表的索引。强制索引将导致查询的其他部分变慢。

#5

Is c.id a primary key? If so, you shouldn't do a DISTINCT on it because it's already distinct, and forcing the DISTINCT may cause it to sort by id then sort by client_name.

c.id是主键吗?如果是这样,你不应该对它进行DISTINCT,因为它已经是不同的,并且强制DISTINCT可能导致它按id排序然后按client_name排序。

#6

Some optimizations are DB vendor neutral while others are DB vendor specific. Here's a couple of things to try.

一些优化是DB供应商中立,而其他优化是DB供应商特定。这里有几件事要尝试。

• Remove the DISTINCT as is suggested elsewhere.

按照别处的建议删除DISTINCT。

• Consider using an inner join. I realize that it may not be a viable option in your situation.

考虑使用内部联接。我意识到在你的情况下它可能不是一个可行的选择。

Also, run an execution plan to get a better picture of what is going on in terms of what parts of the query are taking up the most time and why. See the EXPLAIN keyword for more details.

此外,运行执行计划以更好地了解查询的哪些部分占用最多时间和原因。有关详细信息,请参阅EXPLAIN关键字。

#7

You'll need to force usage of index on client_name:

您需要在client_name上强制使用索引:

SELECT id
FROM (
  SELECT c.id,
    (
    SELECT 1
    FROM client_projects cp
    WHERE cp.client_id = c.id
      AND cp.project_id = 1
    LIMIT 1
    ) e
FROM clients c
FORCE INDEX (client_name)
WHERE c.active_flag = 1
ORDER BY
  client_name
) co
WHERE e IS NOT NULL

#8

Is c.id an identity column? If it is, I don't think you'll need the DISTINCT in there since each c.id will be unique.

c.id是一个标识栏吗?如果是,我不认为你需要DISTINCT,因为每个c.id都是唯一的。

EDIT

So c.id may have multiple entries in cp even if cp.project_id = 1?

所以即使cp.project_id = 1,c.id也可能在cp中有多个条目?

EDIT

Just curios as to why you want to order by client name when you are not selecting it.

只是好奇你没有选择它时为什么要按客户名称订购。

#9

why are you ordering by client name if you are not even returning it?

如果您还没有退回,为什么要按客户名称订购?

Also do you need the distinct?

你也需要与众不同吗?

Why are you doing a left join if your where clause will make it a inner join anyway

如果你的where子句无论如何都会使它成为内连接,你为什么要进行左连接

By having this WHERE cp.project_id = 1 instead of AND cp.project_id = 1 before the WHERE clause it is a INNEr JOIN anyway

通过在WHERE子句之前使用此WHERE cp.project_id = 1而不是AND cp.project_id = 1,无论如何它都是INNEr JOIN