Reputation: 728
I have three tables named users
, cities
and countries
and these two scenarios:
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;
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
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
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