CODEWITHSUNDEEP
mysqlindexing
Bachx
Bachx

Reputation: 447

MySQL query ignores index when using LEFT JOIN with OR?

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?

Upvotes: 3

Views: 836

Answers (3)

Kevin Burton
Kevin Burton

Reputation: 11936

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

Upvotes: 2

nobody
nobody

Reputation: 10645

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

Upvotes: 0

Naktibalda
Naktibalda

Reputation: 14110

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

Upvotes: 0

Related Questions