Joins are slow.
If we use joins in SQL queries, our query will be slow, right?
So, should we avoid using joins and reduce the number of tables?
Let’s find out.
I wrote this tweet about joins not being slow, as I’ve heard and seen people say, “using
more joins will make your query slower, so avoid joins.”
There were a bunch of responses, some of them having opposing views.
I’ve also had people over the years of running this channel and my website ask me if joins
should be avoided.
So, I thought I’d run a test, and create a video about it to show you.
Let’s start the test.
To test this concept, I’ll run a query on a Postgres database with some sample data.
The concepts should work the same on other vendors.
The first query will use three separate tables, which uses a normalised design.
This has the benefits of only storing the data in one place, making updates and other
queries easier.
The second query will find the same result, but will use a single table that has all of
the data.
The goal is to find out which query performs better.
Here’s the database diagram for this database.
It’s a data set of Olympic events, medals, and people.
Our query will find data from three tables: person, person_region, and noc_region.
We’ll find all of the people that represent the country of Italy.
How much data is in each of these tables?
I’ll run a quick Select Count on each of these tables, and we can see there are about
128,000 records in the person table, 130,000 in the person_region table, and 231 in the
noc_region table.
So, they’re not small tables that you might find in examples online, but they aren’t
massive tables either.
Here’s the first query.
We select a few columns from the person table and the noc_region table.
We use inner joins to join the three tables together, because person_region is a joining
table for both person and noc_region.
Finally, we filter on the noc value of ITA which is the value for Italy.
We can run the query and this is what it shows.
In pgAdmin, it shows the number of rows at the bottom, which is 4,654, and it completed
in less than a second.
To find the steps that the database took to run this query, we can view the Explain Plan.
The steps to do this in each editor are different, but there’s usually a toolbar button.
In pgAdmin, I can click here, and see the graphical view of the explain plan.
First, I want to click on this dropdown and ensure Costs is checked, because we want to
see the Cost values, which are an arbitrary number that represents the effort or cost
of a query.
We can read this from left to right.
I’ll have another video that explains how to read this output in different editors in
the future, but I’ll summarise it here.
The database gets the data from the noc_region table, then performs a Hash step.
Then it gets the data from the person_region table and performs a Hash Inner Join to combine
the data.
It then gets the data from the person table using the primary key called pk_person, and
performs a Nested Loop Inner Join to combine all of the data.
We can click on this Nested Loop Inner Join to see the Cost value of the entire query,
which is 2,436.
So, that’s the cost of the query using joins on three different tables.
What if the data was all in one table, so we didn’t have to do the joins?
Let’s try that test now.
I’ll create a new table here called combined_person_region, which is populated using the results of this
Select query without the WHERE clause.
It should have a few hundred thousand rows.
Once this table is created, we can write a query that selects the columns from this table,
and add a WHERE clause to filter where NOC is ITA.
We run the query and see there are still 4,654 results, which is the same as the first query.
We can see the run time is also less than a second.
With the run times this small, less than a second, it’s hard to do a direct comparison.
So that’s why we also look at the cost values.
We can run an Explain Plan on this table, and see what it shows.
There’s just one box, called combined_person_region, which is the table name.
We can click on it and see that the total cost is 2,758.
So, this is higher than the separate tables, which had a cost of 2,436.
Only a little higher though.
Now there’s one feature that you might be wondering about if you know what it is.
It’s a pretty important feature when it comes to databases and performance.
And that is, indexes.
An index is a type of object in a database that is used to improve the performance of
queries in a database.
It’s a common way to improve performance.
I’ve got a separate video here that explains more about what it is and how it works.
Without indexes, the queries have to look up the entire table.
When we use indexes, it should improve the performance.
Let’s create some indexes and see how they impact both of our queries.
There’s a lot of theory behind which columns are the best to create indexes on, but there
are two places where I’ve seen the most benefit.
These are on foreign key columns and columns in the WHERE clause.
Primary keys have indexes created automatically, but foreign keys do not.
Adding an index to a foreign key can help the database find the matching records on
each side of the join.
And, adding indexes on columns in the Where clause can help with filtering data.
So, let’s create a few indexes.
I’ll create three indexes here for the original tables: two on the person_region table for
each of the foreign keys, and one on the noc column as that’s the column in the Where
clause.
I’ll also create an index on the combined table on the noc column, which is used in
the where clause.
We don’t need the indexes on the foreign key columns because they aren’t needed,
as the data is already in the one table.
Let’s rerun the explain plans and see what they show.
We can see a couple of differences here.
The first is on the left, we can see an icon labelled idx_pr_region that is used.
This means this index was used by the database, to find records in the person_region, and
then joined to the noc_region table using this Nested Loop Inner Join, which was a Hash
Join last time.
The rest of the steps are unchanged.
We can click on the top-right step and see the total cost is 822, which is a big improvement
over the 2,436 from earlier.
What about the second query?
We can rerun the explain plan for that, and see a new icon appear on the left, called
idx_comb_noc.
This is the new index we created, and it has been used by the database.
We can click on the step on the right to see the total cost, which is 1,239.
This is also a big improvement over the version without indexes which was around 2,700.
Here’s a summary of the results.
Both queries had improvements in the cost after indexes were created, but the version
with the joins had a lower cost.
You might be wondering about this particular test and how it’s not realistic.
Yes, we could have used some tables with more data, or added more joins to our query to
add more steps that the database would need to do.
However, the point is this: databases are very efficient at retrieving data from many
tables.
That’s the core purpose of a database.
There are a bunch of features built-in to the database to make this work efficiently.
Also, storing data in separate tables in a normalised way has many benefits.
So, don’t avoid using joins because you think they may be slow.
Design an effective database that represents your requirements or the solution you need,
write a query, and observe the performance.
Add an index.
Observe the performance again.
If the query is slow, then you can try to optimise it.
I hope this video helped answer any questions you had about using joins and their impact
on performance.
If you have any questions, let me know in the comments below.