CODEWITHSUNDEEP
mysqlsqlleft-join
alexalok
alexalok

Reputation: 117

MySQL: join with OR clause slows down

I have a simple MySQL InnoDB database with two tables: users and dialogues. I am trying to make a LEFT JOIN query, however, I've ran into a performance problem.

When I execute the following statement,

EXPLAIN SELECT u.id FROM users u
LEFT JOIN dialogues d ON u.id = d.creator_id

I get a response that DB uses SELECT types index and ref, which is totally fine.

However, when I add an additional clause:

EXPLAIN SELECT u.id FROM users u
LEFT JOIN dialogues d ON (u.id = d.creator_id OR u.id = d.target_id)

suddenly the DB indicates that it uses all SELECT type when JOINing, which in turn makes the actual query multiple times slower.

Is there something that could be done to make DB use more effective SELECT type in the second example? d.creator_id and d.target_id columns have foreign keys connected to u.id.

Upvotes: 1

Views: 187

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

It is usually faster to do two left joins and coalesce() in the select:

SELECT d.*,
       COALESCE(uc.name, ut.name) as name
FROM dialogues d LEFT JOIN
     users uc 
     ON uc.id = d.creator_id LEFT JOIN
     users ut
     ON ut.id = d.target_id

Upvotes: 2

Related Questions