Performance at finding all foreigns with 3 one-to-many relation Basically, I have 2 tables : user and object. One user can be associated to one object by three ways (roles). I want to list all users associated at least to one business object by any role.
This query works, but it takes 5 seconds !
SELECT u.id
FROM user u
LEFT JOIN translation_revision t1
    ON u.id = t1.translator_id
LEFT JOIN translation_revision t2
    ON u.id = t2.proofreader_id
LEFT JOIN translation_revision t3
    ON u.id = t3.reviewer_id
wHERE t1.id IS NOT NULL
    OR t2.id IS NOT NULL
    OR t3.id IS NOT NULL
GROUP BY u.id   

Complete fiddle with creation and data here : https://www.db-fiddle.com/f/nojYaUsWicKJDuRQk1Nqd2/7
Any idea on how to improve perf here ?
Instead of doing 3 separate joins, you could accomplish the same thing by simply using an OR clause (the execution time seems to be ~1-2ms):
SELECT DISTINCT u.id
FROM user u
JOIN translation_revision t1
    ON u.id = t1.translator_id OR u.id = t1.proofreader_id OR u.id = t1.reviewer_id
GROUP BY u.id   
LIMIT 30

Other ideas would be to denormalize your data and keep some additional info about your users roles.