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

Question

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?

GMB · Accepted Answer · 2019-10-09 22:27:31Z

1

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

answered Oct 9, 2019 at 22:27

GMB

224k25 gold badges103 silver badges151 bronze badges

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

3 Comments

NaturalBornCamper Over a year ago

Thanks friend, when I use a "JOIN ON .. OR .." the query goes to 1.6 seconds (from 0.06 seconds without "OR"). That's why I was thinking my method was a bad idea (already have indexes)

NaturalBornCamper Over a year ago

Won't using a second JOIN waste resources? I was hoping there's another way of doing this, but I LOVE your "coalesce" technique

GMB Over a year ago

@NaturalBornCamper: that was a suggestion, not sure it will improve performance. Give it a try against your dataset and let me know.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related