Database optimization processes involve a series of smaller tasks that are improved to give better performance. One such job is the SQL Query Tuning. It consists of finding and enhancing slow queries, analyzing query performance, setting indexing strategies to meet the query’s needs, and more. DBAs further breakdown query tuning into multiple sub-tasks like the SQL query execution plan.
Databases create a plan for each query on how it needs to be executed. Out of the many possible ways to run a query, the database evaluates and performs query optimization to determine the best set of actions applied to each query. The result of this is the SQL query execution plan. In today’s article, we will talk more about what these plans are and how they can be used effectively for various databases that exist today.
SQL Query Execution plan
A query Optimizer is an internal component of databases whose job is to find out the best possible way to execute a query in a reasonable amount of time. The output of this task is called a SQL Execution plan. As the name suggests, it’s a detailed explanation that the database records and uses to execute the query. The plan consists of information such as which column to scan first, what kind of operator needs to be applied to the rows, etc.
A plan is needed because a query works on many different components such as indexes, tables, while interacting with the storage engines, logging systems, etc. let’s say that a query needs to extract information from multiple tables and look for information in multiple columns. What order should it follow to get the response in the fastest time possible? When should it perform an aggregation first, or should it sort the data before anything else?
These questions are all the different possibilities or routes the DB server can take to get the final result. With more operations and components, the complexities only grow. This is why optimizers analyze the ways and try to find a path ( the plan ) that fetches the result in a short amount of time.
Image Credit: A Query Execution Plan shown graphically -SQL Shack
The main reason why these plans are essential is that there is a good chance that the plan selected by the database might not be the best one. At times, there are so many possibilities that it’s impossible to find the best strategy to execute a query in a given amount of time. This is where DB developers and database administrators plan and analyze why a query might be running slow or find ways to make the query run faster.
Estimated and Actual Execution Plan
Before we talk about using the SQL Query Execution plan, let’s quickly talk about what estimated and actual query execution plans are.
An Estimated query plan is the one that has been evaluated by the query optimizer as the best path. Every time a query is given to the database, it calculates this. On the other hand, the Actual Execution plan is the path the database took to execute a query and provide the result. Figuring the query plan each time is relatively inefficient. Thus the databases sometimes store the actual execution plan later when the same query is called again. This is stored in the execution plan cache, storage to retain, and update query plans.
Query Execution Plan for various Databases
We can now look at various query execution plans for databases like MySQL, SQLite Database, and the SQL Server.
1. Execution plan in MySQL
MySQL is undoubtedly one of the most widely used databases today. They provide out of the box functionality to get the query plan for any given query using the EXPLAIN command. The EXPLAIN command is used as a query optimization tool, and it works with five different types of queries: SELECT, INSERT, UPDATE, REPLACE, and DELETE.
When used on a query, the EXPLAIN command gives you a row of information for every table that the query will access. The output of this command is a detailed description of the process of query execution. It includes information such as ID, tables, partitions, number of rows that are going to be accessed, and more. The image below gives a full description of the various output columns from the EXPLAIN statement.
Image Credit: Output Formats of an EXPLAIN statement MySQL Docs
The image below shows an example output of executing EXPLAIN on a select query.
2. Execution Plan in SQLite Database
As the name suggests, SQLite is a lightweight database used with low digital footprint devices like mobile phones, edge IoT devices like the Raspberry Pi, etc. Query optimization is necessary irrespective of the database, and just like MySQL, SQLite provides some out of the box tools to get the SQL Query Execution plan.
To get the SQL query execution plan for SQLite queries, you need to use the EXPLAIN QUERY PLAN command. While this command provides the applied strategy to execute a query at a higher level, it focuses mainly on showing how the database’s indices are used.
SQLite has some specific terminologies that it uses. The word SCAN indicates that the entire table is being scanned or examined by the query. And the word SEARCH is used, it means that only a small portion of the table is being examined by making use of the indexes. Assume that we have the following sample query:
SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
To get the query plan, we need to use the EXPLAIN QUERY PLAN as follows:
EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
Following is an example of how the output would look like:
QUERY PLAN
|–SEARCH TABLE t1 USING INDEX i2 (a=? AND b>?)
`–SCAN TABLE t2
If we examine the output above, we can see that SQLite scans through one table while it searches in the other. We can imply that t2 needs indexes to be added so that searching is faster, thereby making the query run faster. This is how we go about doing query optimization.
This official SQLITE guide on query execution plans will help you dig deeper into using the command more efficiently.
3. Fetching Plans with SQL Server
Like the other two databases, the SQL server also provides out of the box functionality to get the SQL query execution plan. With the SQL Server, we will be able to get both the estimated query execution plan and the actual plan straight from the GUI interface.
To get the plan, you need first to select the query you have written on the query editor. Then you can do one of the two things to get the estimated plan:
- Right-click on your mouse and select the “Estimated Query Execution Plan” option.
- You can also press CTRL + L to open the plan.
The plan opens up as a separate section on the editor. Being an interactive view, you can hover over the nodes in the plan, and it will provide you more details about the node as shown in the image below.
Image Credit: SQL Plan and details in SQL Server SQLShack
Conclusion
Database Administrators need to regularly step in to ensure that the query plan is the best it can get to ensure fast query executions. Since the database query optimizer doesn’t always provide the best plan, we can visually or interactively view the SQL query execution plan and determine if it’s good or improved.
We can also use the plan to find regions of improvement. For example, the plan can reveal where indexes can help speed up the query. We saw how we can get the plans for multiple databases and how extracting the plan has been inbuilt into each of them. This feature’s importance can be derived from the fact that irrespective of the databases, they provide the feature to find a detailed explanation of the query execution path. As seasoned DBAs, we often use this feature while performing query optimization, and we highly recommend using it.