CODEWITHSUNDEEP
mysqlsqljoinleft-join
NaturalBornCamper
NaturalBornCamper

Reputation: 3866

MySQL JOIN using either NON-NULL column to reference another table

I have 2 tables filled automatically by cronjobs (an events table and a location table with the addresses of the events. Because both feeds are different, sometimes the events are linked to the locations via location_id1, but sometimes with location_id2, like such:

locations table:
id   imported_id1   imported_id2   address
1          NULL          20           xx
2          10            NULL         xx
...


events table:
id   location_id1   location_id2   some_data
1       NULL           20           xx
2       10             NULL         xx
...

To select the events and get the correct address to the location it's linked to, I tried a JOIN like this, but the OR makes the query run SO MUCH slower:

SELECT * FROM events
JOIN locations ON
    events.location_id1 = locations.limported_id1
    OR events.location_id2 = locations.limported_id2;

Anyone has a better way to query this?

Upvotes: 0

Views: 37

Answers (1)

GMB
GMB

Reputation: 222402

The logic of your query is just fine. To start with, make sure that you have the following indexes in place:

locations(location_id1)
locations(location_id2)
events(location_id1)
events(location_id2)

If indexes are already in place, or if creating them does not significantly improve performance, one thing that you could try is to switch to two LEFT JOINs with a WHERE clause that ensures that one of the joins matched, and COALESCE() to return the adress from the matching join, like:

SELECT l.*, COALESCE(e1.address, e2.address) address
FROM locations l
LEFT JOIN events e1 ON e1.limported_id1 = l.location_id1
LEFT JOIN events e2 ON e2.limported_id2 = l.location_id2
WHERE e1.id IS NOT NULL OR e2.id IS NOT NULL

Upvotes: 1

Related Questions