MySQL query ignores index when using LEFT JOIN with OR?

Question

I have this query:

SELECT f.uid, fi.status FROM friends f
LEFT JOIN friends_invitations fi ON f.fid = fi.fid
WHERE (f.uid = 2 OR fi.uid = 2)

The above query is correct, however performance-wise it's scanning all rows in the friends table, and ignoring the f.uid index in the WHERE clause, despite fid and uid being indexes in BOTH tables.

Basically I want the optimal approach to retrieve a user that exists either in Friends or Friends Invitations table using the 'uid' field.

Any ideas/suggestions?

Kevin Burton · Accepted Answer · 2011-08-05 17:50:01Z

2

how about :

SELECT f.uid, fi.status 
FROM friends f 
LEFT JOIN friends_invitations fi ON f.fid = fi.fid 
WHERE (f.uid = 2) 
UNION
SELECT f.uid, fi.status 
FROM friends f 
INNER JOIN friends_invitations fi ON f.fid = fi.fid 
WHERE fi.uid = 2

The combination of the or and ther left join are making your query full scan

edited Aug 5, 2011 at 17:50

answered Aug 5, 2011 at 15:32

Kevin Burton

12k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bachx Over a year ago

Yep that's what I did, except for 'on fi.uid = 2' on the last line which I assume you meant 'WHERE fi.uid = 2'.

Naktibalda · Accepted Answer · 2011-08-05 14:41:44Z

0

Use UNION:

SELECT f.uid FROM friends f WHERE f.uid = 2
UNION
SELECT f.uid FROM friends f
JOIN f.friends_invitations fi ON f.fid = fi.fid
WHERE fi.uid = 2

answered Aug 5, 2011 at 14:41

Naktibalda

14.2k5 gold badges37 silver badges53 bronze badges

1 Comment

Bachx Over a year ago

Thanks for the answer, but what if I want to retrieve fields from friends_invitations table as well, UNION sadly won't work as it looks for identical fields.

nobody · Accepted Answer · 2011-08-05 15:39:31Z

0

SELECT u.uuid, fi2.status FROM
( SELECT f.uid AS uuid FROM friends AS f WHERE f.uid = 2
  UNION
  SELECT fi.uid FROM friends_invitations AS fi WHERE fi.uid = 2 ) AS u
LEFT JOIN friends_invitations AS fi2 WHERE u.uuid = fi2.uid

answered Aug 5, 2011 at 15:39

nobody

10.7k4 gold badges29 silver badges43 bronze badges

Collectives™ on Stack Overflow

MySQL query ignores index when using LEFT JOIN with OR?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related