CODEWITHSUNDEEP
mysqlperformancejoin
Deimoks
Deimoks

Reputation: 728

Deep joins performance

I have three tables named users, cities and countries and these two scenarios:

1) User belongs to city, city belongs to country (deep join)

Get any user's country:

SELECT country.name
FROM users
LEFT JOIN cities ON user.city_id = cities.id
LEFT JOIN countries ON city.country_id = country.id
WHERE user.id = 1;

2) User belongs to city and country, city belongs to country (one join)

Get any user's country:

SELECT country.name
FROM users
LEFT JOIN countries ON user.country_id = country.id
WHERE user.id = 1;

At first glance, scenario 2 seems faster but, is it a good idea to have country_id FK in users table to save one join? Or should I take advantage of relationships and make a deep join? What of these two scenarios actually perform faster?

Upvotes: 2

Views: 383

Answers (2)

Isaac Hildebrandt
Isaac Hildebrandt

Reputation: 1026

In the first situation you are primary key based lookup on three tables and reducing it to only two tables in the second. That is what I would consider a micro-optimization. You won't see significant performance returns unless the tables are enormous (millions of rows) or writes are happening quickly enough to cause lock contention.

Upvotes: 1

Wolph
Wolph

Reputation: 80111

One join is almost always faster than 2 joins, but the question here shouldn't be which is faster but which is more maintainable (also look at When to optimize).

Are you actually having a performance problem? Even though in this case the data probably never changes (at least, cities usually don't change country) there is still a risk that the data between the tables gets out of date. So the question here is, is it fast enough?

These types of optimisations generally give very little benefit in terms of performance but bring in risks that the data will be out of date and it makes things more complex.

Upvotes: 4

Related Questions