Query Optimisation Insights: Strategies for Slow Queries

Author: Saksham Jain

Embarking on the journey of SQL query optimization begins with a crucial step: comprehending the query execution plan, which serves as your roadmap to achieving optimal performance. By delving into this plan, you unlock the ability to pinpoint precisely where your query is experiencing bottlenecks and time inefficiencies. Armed with this knowledge, you can strategically enhance your queries, whether it involves introducing well-placed indexes, crafting more efficient join operations, or employing other optimization techniques. In this blog, we’ll explore the ways to optimize SQL query, starting with a deep dive into the query execution plan.

Query Life cycle:

Parsing: To check its syntax and semantics, ensuring it is written correctly and adheres to the SQL language rules. During this phase, the query is transformed into a logical representation that the evaluation engine can understand.

Optimizer: The query optimizer analyzes the logical representation of the query and determines the most efficient way to execute it. The optimizer looks for various execution plans and statistics to choose the one that minimises resource usage and execution time.

Evaluation Engine: Once the query optimization is complete, the evaluation engine takes over. It uses the optimized execution plan generated by the query optimizer and starts executing the query against the database.

What is Indexing?

Indexing is the process of creating a data structure that improves the speed of data retrieval operations on a database table.
Indexes are used to find rows with specific column values quickly.
Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

show index from TABLE_NAME;
create index INDEX_NAME on TABLE_NAME (col1,col2,col3);
drop index INDEX_NAME on TABLE_NAME;

Working of indexes : Index works to rapidly identify the rows that match a WHERE clause or to exclude certain rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index).
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.
For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on
(col1),
(col1, col2),
(col1, col2, col3).

Cardinality of index : Cardinality, in the context of an index in a database, refers to the uniqueness or distinctness of the values within the indexed column or set of columns. It represents the number of unique values present in the indexed column(s). The cardinality of an index is a crucial factor considered by the query optimizer when deciding whether to use the index for a particular query. High cardinality indexes are more likely to be chosen because they are more selective and can reduce the number of rows that need to be examined.

Explain Command:

explain <query>;

Select_type: Type of Query eg: SIMPLE , PRIMARY, SUBQUERY
Table: Name of Table
Partitions: The partitions from which records would be matched by the query. The value is NULL for non-partitioned tables.
Type: Type of Access_JOIN eg: all, const, ref, range
Possible Keys : The indexes from which MySQL can choose to find the rows in this table
Key : the key (index) that MySQL actually decided to use
Key_len: The value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses.
Ref: The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
Rows: The rows column indicates the number of rows MySQL believes it must examine to execute the query.
Filtered: indicates an estimated percentage of table rows that are filtered by the table condition.
Extra: This column contains additional information about how MySQL resolves the query.

Explain analyze Command :

explain analyze <query>;

This command in SQL is a powerful tool that allows you to see how your query is being executed by the database. It provides detailed information on the execution plan, including the order in which tables are accessed and the methods used to retrieve data.

One of the biggest benefits of using (explain analyze) command in SQL is that it allows you to identify and optimize slow-running queries. By analyzing the execution plan of a query, you can pinpoint exactly where the bottlenecks are and make targeted improvements.

explain analyze
Select * from testTable rmc
where rmc.date >= ‘2023–01–01’ and rmc.date <= ‘2023–01–31’
and rmc.memberId in (32434324)
and rmc.month = 9
and rmc.year = 2023
and rmc.age = 65;

Output:

Index range scan on rmc using IDX_date_member_year_month_age,
with index condition: ((rmc.age = 65) and
(rmc.year = 2023) and
(rmc.month = 9) and
(rmc.memberId = 32434324) and (rmc.date >= DATE’2023–01–01') and (rmc.date <= DATE’2023–01–31'))
(cost=80951.20 rows=68544) (actual time=3.350..9.605 rows=152 loops=1)

How to read output of explain analyze

cost=80951.20 rows=68544 : This is the cost and the row number that is estimated.
actual time=3.350..9.605 rows=152 loops=1:
3.350 — Time to return first row (first Read) in milliseconds.
9.605 — Time to return all rows (all Read calls) in milliseconds.
rows=152 — Number of rows returned by this iterator. Finally, it is the exact number.
loops=1 — Number of loops (number of Init calls).

Forced Index:

SELECT *
FROM TABLE ru force INDEX (IDX_ABC)
where ………..

If you have a query that consistently performs poorly, despite having appropriately designed indexes, you might force an index that you know performs better for that specific query. This is typically done after exhausting other optimization techniques.

In some cases, you might have a table with historical data, and the query optimizer may not choose the optimal index for older data. Forcing an index can be a temporary solution to improve performance for historical data queries.

During query tuning and debugging, you might force different indexes to compare their performance. This can help you understand how the query optimizer’s choices impact performance and guide you in making better indexing decisions.

Before using a forced index, it’s crucial to thoroughly analyze the query, understand the data distribution and cardinality, and consider other optimization techniques, such as proper indexing, query rewriting, and database schema improvements. Forced indexing should only be used when you have exhausted other options and have a deep understanding of the query and database performance characteristics.

Forced indexes are not automatically maintained by the database system, so you must monitor and maintain them yourself. This includes periodically checking if the forced index is still beneficial as data volumes change.

Why did the database index go to therapy?

Because it had too many commitment issues — it couldn’t decide whether to speed up SELECT queries or slow down INSERT and UPDATE queries!

Indexes offer significant benefits like faster query performance, but they also come with some drawbacks:

• Indexes consume disk space and memory.
• Index maintenance overhead (rebuilding or reorganizing).
• Unnecessary Indexes causes queries to take more time.
• Indexes can slow down write operations.

Conclusion :

To ensure indexing provides benefits and does not cause harm, it’s essential to carefully analyze the query workload, data distribution, and system requirements.