So, you might know a little about SQL query performance, but it’s just theory.
In this video, we’ll put it into practice with a real query.
We’ll see a query that is not very efficient, and make some changes to get a big improvement
in the cost and efficiency of the query.
Here’s the query we will be using.
It’s the same query I covered in my Complex SQL Breakdown video where I showed the steps
to understand what this query is doing.
Also, I’m using MySQL Workbench and a MySQL database to show this example, but the concepts
should work on other databases such as SQL Server, Oracle, and Postgres.
The query has a few tables, and essentially it’s showing names of people and the results
of challenges they have completed.
We can see there are a couple of subqueries here, some inner joins, some left joins, some
Group By clauses, a Having clause, and an order by at the end.
Here’s an Entity Relationship Diagram to help visualise the different tables.
I’ll have a link to this in the description.
Let’s start by running this query.
We run the query and it finishes in under a second.
So it’s pretty quick.
But this is due to the small amount of data for this example.
Realistically you may be trying to improve the performance of a query that takes much
longer, or a query that takes a few seconds but is run many times.
So, here are our results.
We can see some ID values, a name, and a few numbers.
The first step I like to take to see how a query performs is to show something called
the explain plan.
This is an estimate of how the database will run your query.
The way to view the explain plan is different in each SQL editor.
Usually there is a button on the toolbar to show it.
I’ll click this button here in MySQL Workbench.
Here we can see the plan.
It’s shown differently in each SQL editor, but in MySQL Workbench it’s shown as a series
of lines and boxes.
How do we read this plan?
In MySQL Workbench, you read the plan from left to right, and bottom to top.
We can start on the left here.
The first step the database performs is this red box here, with the words Full Table Scan.
A Full Table Scan is the type of operation done on the table, and in short it means that
all of the data in the table is read.
The letter c underneath the box refers to the table name or the table alias.
So, in our query, we can find the table of c to see which one this is.
In our query, the letter c refers to the contests table.
The number on the top left of the box, the 16.65, refers to the cost of this step.
The cost is an arbitrary number that represents the effort involved in this step.
The lower the number, the better.
On the top right of the box is the number of rows that are estimated to be involved
in this step.
In this example, there are 164 rows.
The next step is the diamond here with the word “hash join” inside it.
This is an operation to combine data from two tables.
What is the second table?
We can look down to see the other arrow going into this step.
This also refers to a Full Table Scan on a table called “cl”, which is short for
colleges.
So, it’s joining the contests table to the colleges table.
We can keep going to the right and see the next step is another hash join to the “ch”
table, which is the challenges table.
Above each of the hash join boxes is a number, and this represents the cost so far, which
is all of the previous steps combined.
We can see the number keeps increasing, because more steps are applied.
The next box looks different.
There is a green box that says Non Unique Key Lookup, and within that are more boxes.
We have something that says “ss materialised”, then a box that says Group, and a full table
scan on submission_stats.
This is actually how the subquery on submission_stats is represented.
It does a full table scan on the submission_stats table, then does a Group By operation, and
is then transformed into the subquery called “ss”.
The diamond above this is labelled with “nested loop”, which is another type of join operation.
The next box to the right is almost the same, but does a subquery on a different table.
Finally, above the last of those boxes, we have a red box that says Group.
We move to the right and a box says Order.
Finally, on the top right we have a grey box that says “query block 1”.
The important number to see on this screen is the Query Cost on the top right.
This number says 11,708,922.
Yes, that’s 11 million.
It doesn’t mean it will take 11 million seconds to run.
It’s a measurement of the effort involved in the query.
We saw earlier the query ran pretty quickly, in under a second.
But we can see it’s not very efficient with a number of 11 million.
We also see a lot of red boxes in the explain plan.
Generally, red boxes are slow operations and green boxes are fast operations.
So, what can we do?
We’re going to look at this query and see how we can improve the performance of it,
and the way it runs.
We measure it by looking for changes in the Query Cost number here.
Where can we start?
If we look at the plan, we can see a bunch of red boxes.
We see they all have the label of Full Table Scan.
This means all of the data in the table is read at each point, which is quite a slow
and inefficient step.
A great way to improve this is to create an index.
This may cause the query to use the index to read the data, rather than the table, which
makes it perform better.
I’ve got a video here all about indexes if you want to know more about them and how
they work.
What do we create an index on?
We’ll start with the table on the far right here, as it has the most rows.
It may not help, and you may want to start at a different table, but we can just pick
this one to start.
This is the view_stats table.
How do we know what column to create an index on?
Let’s look at our query.
A good column to create an index on are columns used in joins, because they are used to look
at values and perform matches.
So, what is the data in the view_stats table being joined on?
We can see at the end of the subquery here the column of challenge_id is used.
It’s being found in the subquery, but it’s still being retrieved from the table.
So, let’s do that.
We’ll create an index on the view_stats.challenge_id column.
Here’s the command to do that.
Notice the name of the index here.
I’ve called it idx_vs_challenge, so I can tell by looking at it that it’s for the
view_stats table and something to do with the challenge column.
Now that the index is created, let’s check the plan again.
Here’s the explain plan again.
There are a few things to notice.
First, the overall query cost did not change.
It’s still at 11 million.
That’s not ideal.
It means our new index didn’t make any improvement overall.
Next, we can look further down the plan to that step on the view_stats table.
We can see the red box now says Full Index Scan instead of Full Table Scan.
This is better.
It’s using the index, and we can see the name underneath the red box, instead of the
Full Table Scan.
But it’s not making any difference to the query.
What can we do next?
Let’s try creating an index on the leftmost table.
In this plan, this is the table “c”, which is the contests table.
In our query, we can look for columns that the contests table uses in a join.
We can see that the contest_id is being used in a join, so let’s create an index on that.
Here’s our command to create an index.
We’ll run it, and the index is created.
Let’s run the plan again.
We can see the plan here has been updated.
There are a few things to notice.
First of all, the query cost has changed.
It’s now showing as 718,247.
This is a pretty big improvement over the 11 million, just by adding a couple of indexes.
We can look further down the plan and see that the steps have changed.
There’s a box here called “Non Unique Key Lookup”, and it’s green.
This means it’s an optimal step.
We see the index name underneath, which is the one we just created.
We also see that this “c” table has been moved to the second step in the plan, instead
of the first step on the left where it was before.
That’s OK, the database has determined a different order to get the data.
So, overall, we’ve got a decent improvement from this step of adding the index.
What else can we do?
Let’s try creating another index.
We’ll look at the next table in the plan, which is the “cl” table now on the left.
In our query, we can see that it’s being joined to the contests table on the contest_id.
We can also see the college_id being used in another join, but we’ll come back to
that.
Let’s create an index on the contest_id.
We can run this command, and the index is created.
Let’s check the plan again.
We can see in the plan that the total cost is the same, at about 718,000.
We can see the first step remains unchanged.
This is OK.
If we look closely though, we can see the first step is now the “c” table, and the
second step is the “cl” table.
This second step uses the index we just created.
So, the database uses our newer index, but the overall cost is the same.
What else can we do?
Let’s look at creating an index on this ch table, which is called challenges.
The table is being joined on the college ID, so let’s create an index on that.
Once we create an index, we’ll check the updated plan.
We can see the query cost is now 43,661.
This is another big improvement!
We can look at the details of the plan and see another green box has emerged.
Our plan now shows us running a full table scan on the contests table, then using indexes
to join data in the colleges and challenges table.
So far so good.
Can we get it to run any better?
We have indexes on many of the join columns, but there’s one we are missing.
It’s the college_id in the college table.
Let’s create an index on this.
We can rerun the plan and check the results.
We can see the overall cost is unchanged, and none of the steps have changed.
So, the query didn’t use this index either.
In the plan, we can see there is a Full Table Scan being used on the submission stats table.
We could improve this step by adding an index on the column being joined.
In the query, we can see the submission_stats is inside a subquery, but it uses the challenge_id
column.
We can create an index on this column by running this command.
Now the index is created, let’s check the plan.
We don’t see any change to the cost.
It still shows as 43,661.
We can see the step inside the plan changes from a full table scan to a full index scan,
which is a better step.
So, we’ve got a bunch of indexes on columns, and the cost is showing as about 43,000.
This query runs pretty quickly, but let’s say it was still slow and we wanted to make
it faster.
What else can we do?
One thing we could do is change how the query is written to reduce the need for some steps.
We’ve looked at how to optimise each step, but sometimes we need to look at removing
steps entirely.
Let’s look at our query.
We can see that there is a group by within each subquery.
We can also see a group by in the outer query, along with a having.
The having clause says to eliminate rows that don’t meet this criteria.
We’re doing the elimination of rows in the final step here.
Perhaps we can move this Having clause inside each subquery, so we can eliminate the rows
we don’t need earlier in the process, which results in less data being used and hopefully
better performance.
Let’s try that.
We’ll move the having clauses for each column inside the appropriate subquery.
Two checks go into the first one, and two into the second one.
Here’s what our query looks like.
We can run the query and see our results.
The query runs and shows us similar to what we saw before.
When I make a change like this to a query, I would normally check that the results match
what we are expecting.
But for the purpose of this video, we can assume they are the same.
Now, let’s check the explain plan.
We can see the plan here and it shows no change.
The steps are the same and the overall cost is the same.
So, that didn’t work.
One more thing we can try is creating temporary tables.
We can see in our query there are two subqueries being used.
Both of these subqueries are similar.
They show a challenge ID and perform two sum calculations, and a group by, and now a having
clause.
This grouping operation is performed each time the query is run.
We could create a temporary table to store the results of this subquery, including the
grouping and summing.
Then we can refer to this summary table in our main query.
This may not work if the data is getting updated very frequently, but it may work for this
query and it may work in your situation.
How do we do this?
First, we’ll write a Select query to show the data we want in our temp table.
This is just a copy and paste of what’s in the subquery.
We can run this query to see what it shows and to check that it can actually run.
Next, we can create a temporary table that uses this select query.
To do this in MySQL, we add the words Create Temporary Table in front of it, then a table
name.
I’ll use the name of summary_ss as it’s a summary of the submission_stats data.
Then, we run this, and the table is created.
A temporary table is not stored permanently and is removed at the end of the session.
We’ll do the same thing for the view_stats table, and now two tables are created.
Now, we can update our main query.
We’ll remove this subquery and just join to the table called summary_ss.
We can leave the join condition the same.
We can do the same thing with the second subquery.
Our main query looks like this.
We’ll run the query to check the results.
We can see the results look the same, and we have the same number of rows, which is
164.
Now, let’s check the explain plan.
Wow, look at that.
The query cost is way higher.
This shows about 41 million.
So what we’ve done has made it much worse!
How can we fix it?
We can look at the details of the plan and see that those subquery steps are much simpler.
There are no boxes within boxes.
However we see two red boxes here, both of which say Full Table Scan.
This means there are no indexes being used.
These are the new temp tables we created.
Let’s create some indexes on these tables.
We’ll create an index on the challenge ID in both temporary tables.
Once the indexes are created, we’ll check the plan again.
Aha!
Look at that cost.
It now shows 717.
This is the best we’ve seen so far.
If we look at the details, we can see a red box on the left, a bunch of green boxes, then
a group by.
We’ve added a few steps to our query in order to create the temporary tables, so we
should also include those in our plan.
We can check the plan cost for those queries by scrolling up in the query, and running
an explain plan on each of those Select statements.
We can see that the first one has a cost of 90, and the second one also has a cost of
90.
So, overall, the cost to get this result is 90 plus 90 plus 717, which comes to 897.
This is much better than before.
There is one more step I’d like to try.
We can see in our query that we’re joining to two summary tables.
These tables show the challenge ID, and the sum of submissions and views.
What if we could combine these two summary tables into a single table?
We could have a column for the challenge ID, and then four columns, one for each sum value.
This could mean that there is only one summary table to join to, removing a whole step from
our query.
Let’s try that.
We’ll write this Select query to combine the data from both the submission stats and
view stats tables.
We’ve essentially combined both of the queries for the summary tables.
I’ve used a left join so that the data that exists in one table is not removed if it doesn’t
exist in another table.
Depending on the data, we may need to include a full outer join here.
But the joins are done on the outer query anyway, so the source of challenge IDs is
the outer query.
We then show the having clause on all four columns.
We can run the query and see the results.
We have five columns here.
Let’s check the explain plan.
We can see the total cost is 987, which is already higher than our previous step.
Let’s create the temporary table anyway to proceed with this idea.
Next, we’ll create an index on this table so we get a good operation when joining to
it.
Finally, we’ll update the main query to join to this new summary table.
We can run the explain plan to check how it performs.
We can see the cost is now 608 overall.
We can add this to the create temp table and get a cost of 1,595.
So this is worse than having separate temporary tables.
Here’s our query in the previous step.
We have two queries to create two temporary tables, then a main query that joins to them.
This query had a combined cost of 897.
Compared to the initial cost of 11 million, this is a massive improvement of over 99%.
The runtime on this query isn’t all that different, but that’s because the data set
is quite small.
On a large data set, the run time could be significantly reduced.
So, in summary, we’ve added some indexes on the table columns that were in the joins,
and created two summary tables to help with combining data.
I would probably want to go back and remove any indexes that I created that are not being
used in this query, because we don’t need them.
I hope this video has helped you see an example on how we could improve a query’s performance,
and given you some ideas on what you can do with your own queries.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.