Optimizing SQL Queries for Better Performance Using Execution Plan

In my previous post, I explored how indexing can help improve SQL query performance. While indexes help improve query execution time, they cannot compensate for poorly written queries. This is why understanding how your database executes queries is important. To achieve this, we need a solid grasp of the Query Execution Plan.

Query Execution Plan

A Query Execution Plan is a blueprint that the database engine uses to execute your SQL query. When you write and run a query, the database does not execute it exactly as you write it. Instead, the database optimizer analyzes the query and determines the most efficient way to retrieve the data.

The execution plan shows the sequence of operations the database will perform, such as scanning tables, using indexes, joining tables, or sorting results. By reviewing the execution plan, you can identify performance bottlenecks such as full table scans, unnecessary joins, or missing indexes.

Understanding execution plans is an essential skill in query optimization. It allows you to see how your query behaves internally rather than just observing the final output. This insight helps developers rewrite queries, add appropriate indexes, or restructure database schemas to improve overall performance.

In most relational database systems, execution plans can be generated using built-in commands or visual tools provided by database management interfaces. Learning how to interpret these plans is the first step toward effective SQL performance tuning (a topic worth exploring).

How to generate a Query Execution Plan using SQL

In MySQL, you can use the EXPLAIN statement to view the query’s execution plan. This is the primary tool for analyzing how the database engine processes your SQL query.

To generate an execution plan, simply place EXPLAIN before your query:

EXPLAIN SELECT * 
FROM employees
WHERE department_id = 10;

The output will show important information such as:

• Which indexes are being used
• Whether the query performs a full table scan
• How tables are joined
• Estimated number of rows processed
• Query execution cost estimation

If you want more detailed insights, you can add the ANALYZE keyword after EXPLAIN:

EXPLAIN ANALYZE SELECT * 
FROM employees
WHERE department_id = 10;

This version executes the query and provides actual runtime metrics, helping you compare estimated performance against real execution performance.

Using EXPLAIN is an essential step in SQL optimisation because it allows developers to evaluate query efficiency before applying indexing or rewriting queries.

Understanding the Query Execution Plan

The execution plan is one of the most powerful tools for improving database performance in MySQL. Instead of guessing why a query is slow, the execution plan shows how the database engine processes your query internally.

When reading an execution plan, think of it as a step-by-step workflow that shows how the database retrieves data.

Key Things to Look for in an Execution Plan

1. Table Access Type (type column)

The type column shows how the database is accessing the table data.

• ALL – Full table scan (slow for large datasets)
• ref – Index lookup using non-unique index (good)
• range – Index scan within a specific range
• const/system – Very fast lookup, usually for primary key or unique index

👉 As a rule of thumb, avoid queries that result in ALL type scans when working with large tables.

2. Index Usage (key and possible_keys columns)

• possible_keys shows indexes that could be used.
• key shows the index actually used.

If key is NULL, it means the query is not using any index.

This is often a strong signal that you may need to review your indexing strategy.

3. Number of Rows Scanned (rows column)

This column represents the estimated number of rows the database engine expects to examine.

The smaller the number, the better the performance.

For example:

• Scanning 50 rows is better than scanning 5000 rows.

4. Extra Information Column

The Extra Column provides additional hints about query execution behaviour.

Common messages include:

• Using where – Filtering is applied after data retrieval
• Using index – Query is covered by index (very efficient)
• Using filesort – Sorting is happening outside index optimization (may be slow)

If you see Using filesort, consider adding an index that supports your ORDER BY clause.

Interpreting Execution Plans in Practice

When analyzing execution plans, focus on the following sequence:

1. Check whether an index is being used
2. Look at the table access type
3. Review the number of rows scanned
4. Examine any warning signs in the Extra column

Remember that execution plan optimization is an iterative process; you may need to test different indexing strategies and query structures.

When Execution Plans Matter Most

Execution plans are especially important when working with:

• Large datasets
• Complex joins
• Reporting queries
• Search filters
• Sorting and aggregation operations

Understanding Different Types of Scans and Joins in Execution Plans

In MySQL execution plans, the optimizer chooses different access methods depending on the query structure, available indexes, and data distribution.

Understanding these Scan and Join strategies is important because they directly affect query performance.

1. Sequential Scan (Full Table Scan)

A sequential scan happens when the database reads every row in a table to find matching data.

This is usually represented by:

• type = ALL

Characteristics:

• No index is used
• Performance decreases as the table size increases
• Often indicates missing or ineffective indexing

Sequential scans are acceptable only for small tables.

2. Index Scan

An index scan occurs when the database uses an index to locate data instead of scanning the entire table.

** Ensure that your query is using the most appropriate index; avoid redundant or unused indexes.**

3. Nested Loop Join

Nested loop joins are commonly used when joining small tables or when an indexed lookup is available.

How it works:

• The database selects rows from one table
• For each row, it searches for matching rows in the second table

This method is efficient when:

• One table is significantly smaller
• Proper indexes exist on join columns

Otherwise, performance can degrade quickly.

4. Hash Join

Hash joins are optimized for handling large datasets.

Execution process:

1. The database builds a hash table from one dataset
2. It then probes the hash table using values from the second dataset

Hash joins are usually faster than nested loops when:

• Joining large tables
• Equality-based joins are used
• If you see Hash joins in your execution plan, consider using INNER JOIN or LEFT JOIN with proper indexes

5. Merge Join (Sort Merge Join)

Merge joins are used when both datasets are already sorted.

Steps involved:

• Sort both tables based on the join key
• Merge matching rows

This method is efficient when:

• Data is pre-sorted
• Indexes support the sorting order

6. Sort

Sort is an expensive operation, especially if it’s done on large datasets. The execution plan will show if sorting is being done in memory or if a disk-based sort is necessary.

If sorting is required, ensure that the column being sorted has an index or that your ORDER BY clause is well optimized.

How to use the execution plan to optimize queries

Once you have evaluated the execution plan, you can proceed to start identifying potential inefficiencies and bottlenecks. Here are some common optimization techniques:

• Indexing: If the execution plan shows a full table scan (e.g., a Seq Scan), consider creating an index on the columns used in the WHERE clause. This can significantly reduce the number of rows scanned and improve query performance.
• Rewriting the Query: Sometimes, simply rewriting the query to change the order of operations or the way joins are written can drastically improve performance.
• Avoid functions in WHERE Clause: Functions like UPPER() and LOWER()in the WHERE clause can prevent the database from using Indexes efficiently. Trying to avoid using functions on index columns.
• Limit the Data retrieved: Use Limit, OFFSET or WHERE clause to retrieve only the necessary rows. By doing this, you reduce the amount needed to be processed.
• Analyse Join Types: If you’re using an INNER JOIN but the execution plan shows a Hash Join or Nested Loop Join, review whether the join strategy is optimal. In many cases, adding or improving indexes on the join columns can influence the planner to choose a more efficient join method.

By now, you should have a clear understanding of why query execution plans are so valuable. They reveal how the database engine processes your SQL statements, step by step. By carefully analyzing these plans, you can spot inefficiencies and make targeted optimizations to improve overall query performance.

Thank you for reading. In my next post, I’ll continue the Optimising Queries series and dive deeper into practical techniques for improving query performance.