SQL - Execution Order
Russell Morris
- 4 minutes read - 773 wordsSo firstly understanding
So the point of this post is to understand how the SQL engine (and this is platform agnostic) looks and understands your query.
Whilst the improvements in DB engines have been amazing over the years this topic is fundamental understanding to how the DB engine will interpret your query commands - it can mean the difference between a really happy DBA or one that is reaching for the access remove script.
So what do you mean execution order
This is how the DB engine sees you query, for example lets take this query to begin with:
SELECT fname, sname, age, hobbies
FROM users
JOIN hobbies ON users.user_id = hobbies.userid
ORDER by fname
Nice and simple query I think we will agree but lets view this through the DB engine eyes:
- FROM users JOIN hobbies ON users.user_id = hobbies.userid
- WHERE - N/A
- GROUP BY - N/A
- HAVING - N/A
- SELECT - SELECT fname, sname, age, hobbies
- ORDER BY - Order by fname
- LIMIT/OFFSET - N/A
Now this is a nice and simple query, and goes through the levels quite nicely. But lets look at these levels a bit more
- FROM - So this is where you data is coming from be it tables/views or other initial filter choose you join wisely
- WHERE - Second filter, lets remove the data that we are not using
- GROUP BY - Are we grouping the results and if so by what - in conjunction with SUM() AVG() etc
- HAVING - Its the where statement but looking at grouped totals (SUM AVG etc)
- SELECT - What data are we returning to the client (all columns or a subset)
- ORDER BY - Are we ordering the data
- LIMIT - Are we putting a limit on the data
So still not sure what this means to me though
This is key to query optimisation and improvement. If we look at the levels above, we should imagine this as and upside down triangle with the FROM level being the widest and LIMIT being the point.
Lets take the above example - but this time I want to know if any one called Veronica has any hobbies.
SELECT fname, sname, age, hobbies
FROM users
JOIN hobbies ON users.user_id = hobbies.userid
ORDER by fname
A bit more information on these tables
there are 1 hundred rows in the users table and 2 hundred in the hobbies table
lets see how the engine would work now:
- FROM - FROM clause will retrieve 300 rows (all rows then join the tables)
- WHERE - N/A
- GROUP BY - N/A
- HAVING - N/A
- SELECT - fname, sname, hobbies (200 rows to the screen (approx))
- ORDER BY - N/A
- LIMIT - N/A
Now lets try this with a WHERE clause:
SELECT fname, sname, age, hobbies
FROM users
JOIN hobbies ON users.user_id = hobbies.userid
WHERE fname='Veronica'
ORDER by fname
A bit more information on these tables
we have 3 veronicas in the table and 1 has a hobby
lets see how the engine would work now:
- FROM - FROM clause will still retrieve 300 rows
- WHERE - WHERE Fname = Veronica - Slices this to 1 row
- GROUP BY - N/A
- HAVING - N/A
- SELECT - fname, sname, hobbies (1 row to the screen)
- ORDER BY - N/A
- LIMIT - N/A
In essence the idea here is to try and reduce the amount of data that you are returning via your query as early as possible.
ORDERING AND LIMIT
I often have conversations with people where they say why is my query running slowly, I am only returning the latest entered row, but its so slow. For the purposes of this blog entry I am not going to optimise the query but highlight why this is slow:
So the table in question has 200 million rows, I want the last one entered:
SELECT *
FROM mybigtable
ORDER BY entry_date
LIMIT 1
Lets see what the data engine has to say:
- FROM - FROM clause will retrieve 200 million rows
- WHERE -N/A
- GROUP BY - N/A
- HAVING - N/A
- SELECT - *
- ORDER BY - entry date - some 200 million rows to order here
- LIMIT - Just show me one record (LIMIT 1)
So as you can see even though you are only showing the last one record, the data engine has to look through the entire data set to get it - this will lead to poor performance. Queries should always be written in the most optimal way - wether you are using an ORM or straight into a db client. If you are nice to it - the db engine can be nice to you.