Weirdly slow Mysql Query on OR-CodePudding

I have the following query that takes 3 seconds

SELECT  SQL_NO_CACHE e.Id
    FROM  `Email` e
    LEFT JOIN  basedb.emailconnections ec  ON ec.id = e.EmailConnectionId
    LEFT JOIN  historydb.`event` ev  ON ev.EmailId = e.Id
    Where  e.UserId = 228
      OR  ev.PartnerId = 150
      AND  ev.Private = 0;

the problem comes from (e.UserId = 228 OR ev.PartnerId = 150 AND ev.Private = 0)

if I run only the left part from the WHERE like this, it takes 0.04 seconds

SELECT  SQL_NO_CACHE e.Id
    FROM  `Email` e
    LEFT JOIN  basedb.emailconnections ec  ON ec.id = e.EmailConnectionId
    LEFT JOIN  historydb.`event` ev  ON ev.EmailId = e.Id
    Where  e.UserId = 228;

and if I run the right part like this, it takes 0.03 seconds

SELECT  SQL_NO_CACHE e.Id
    FROM  `Email` e
    LEFT JOIN  basedb.emailconnections ec  ON ec.id = e.EmailConnectionId
    LEFT JOIN  historydb.`event` ev  ON ev.EmailId = e.Id
    Where  ev.PartnerId = 150
      AND  ev.Private = 0;

so it is way faster if I run 2 queries insted of running a single one and the result count from the two faster onces match the slow one

SQL_NO_CACHE is only for debuging

CodePudding user response：

AND is usually faster than OR.
The tests for one table will be used first, then it will "join" to the other table and filter by it. This limits the efficiency of a JOIN and WHERE.

Check this;

Where  e.UserId = 228
  OR  ev.PartnerId = 150
  AND  ev.Private = 0;

is the same as

Where  e.UserId = 228
   OR  ( ev.PartnerId = 150  AND  ev.Private = 0 );

If that is not what you wanted, change the parentheses.

These indexes may help with the first query (with the AND/OR parenthesised as stated):
```
e:  INDEX(UserId, EmailConnectionId,  Id)
ev:  INDEX(EmailId)
```
As mentioned in the Comments, a UNION is often the way to speed up OR.