mySQL JOIN在WHERE子句中使用AND导致查询缓慢

I am trying to do a query to find emails for a specific user where the user either wrote the email (stored in the email table itself) or was cc'ed in the email, stored in another email that is joined to the email table in the query.

我正在尝试进行查询以查找特定用户的电子邮件,其中用户编写了电子邮件(存储在电子邮件表本身中)或者在电子邮件中被抄送,存储在另一封加入电子邮件表的电子邮件中查询。

This query is very slow. There are about one and a half million emails and a token number of entries in the email_cc table; but the OR in the WHERE clause turns the simple query against the email table into something that takes a factor of 1,000 longer.

这个查询非常慢。 email_cc表中有大约一百五十万封电子邮件和一个令牌数量的条目;但是WHERE子句中的OR将对电子邮件表的简单查询转换为需要1000倍的时间。

How do I rewrite this query to make it performant?

如何重写此查询以使其具有高性能?

**email**
id (PRIMARY)
userid (INDEX)

**email_cc**
id (PRIMARY)
userid (INDEX)
emailid (INDEX)

SELECT email.id, email.userid
FROM email
LEFT JOIN email_cc ON (email_cc.emailid = email.id)
WHERE (email.userid = 5 OR email_cc.userid = 5)

In response to comments: running this query:

回复评论:运行此查询:

EXPLAIN SELECT COUNT(DISTINCT email.id) FROM email 
LEFT JOIN email_cc ON email_cc.emailid = email.id 
WHERE (email.userid = 468741 OR email_cc.userid = 468741)

Gets

SIMPLE  email   index   IDX_54469DF4217BBB47    IDX_54469DF4217BBB47    5   NULL    1514520 Using index
SIMPLE  email_cc    ref IDX_8D675752700047D2    IDX_8D675752700047D2    4   email.id    1   Using where

2 个解决方案

#1

I would suggest you to use union as it would be comparatively faster than or. Something like this:-

我建议你使用union,因为它会比或者更快。像这样: -

SELECT email.id, email.userid
FROM email
LEFT JOIN email_cc ON (email_cc.emailid = email.id)
WHERE email.id = 5

union

SELECT email.id, email.userid
FROM email
LEFT JOIN email_cc ON (email_cc.emailid = email.id)
WHERE email_cc.id = 5

"or" is causing a table scan on the email table even if there are indexes on both email.id and email.userid. A union will convert that into index seek + index scan. (assuming there are indexes for two columns)

“或”导致电子邮件表上的表扫描,即使email.id和email.userid都有索引。联合会将其转换为索引搜索+索引扫描。 (假设有两列索引)

#2

Can you give this a try

你能尝试一下吗?

Updated as per OP's comment

根据OP的评论更新

SELECT 
  email.id, 
  email.userid
FROM 
  email
WHERE 
  email.id = 5
  OR EXISTS ( SELECT 1 FROM email_cc WHERE email_cc.id = 5 )

#1