MySQL Query Optimization: Why the Optimizer Skips Indexes

Oct 27, 2023

Mins to Read

All

Imagine you're searching for a specific book in a huge library. The librarian is like a magic book-finder, instantly locating what you need among thousands of books. In the world of databases, something similar happens with a process called query optimization. Its job is to make sure you get your data quickly. But here's the catch: this magic doesn't always use the fastest methods, like using bookmarks called indexes. Instead, it might go through the entire library, which can be slow and resource-intensive.

In this blog, we'll explore why this happens, understand how query optimization works, and learn practical tips to speed up your MySQL queries, even when the optimizer doesn't use its shortcuts. So, let's dive into the world of MySQL query optimization and find out how it all works!

Read: MySQL Explain Format: Optimizing Query Performance in MySQL 8.0.32 to get more insights.

Key Features of MySQL Query Optimization

If you've ever wondered why the optimizer sometimes ignores indexes, it all comes down to how this optimizer works. Its main job is to make sure your database queries are as fast as possible. As its name implies, the primary role of the optimizer is to assess and enhance the execution plans for database queries, guaranteeing that queries are executed with maximum efficiency.

The database query optimizer employs a blend of algorithms, heuristics, and cost-based analysis to identify the optimal query execution plan. Below, we'll explore some key aspects of the optimizer to shed light on its inner workings.

‍

To generate execution plans, the optimizer employs a cost model that estimates the expenses associated with various operations during query execution. Furthermore, the optimizer maintains a database of these cost approximations for the development of execution plans. These estimates are stored in the server_cost and engine_cost tables within the MySQL system database and can be customized as needed. These tables are designed to allow adjustments to the cost assessments used by the optimizer in its pursuit of optimal query execution plans.

The Role of Indexing in MySQL Query Optimization

How MySQL Query Optimizer Utilizes Indexes

An index, which we often create with the assumption that it will be used by the optimizer, doesn't always guarantee its utilization. Despite our careful planning, the optimizer may, at times, choose not to use an index.

Indexes are critical for ensuring a quick scan of the table and delivering efficient results. However, the absence of index usage by the optimizer doesn't render the index worthless. There are various scenarios where indexes might not be employed. Let's explore some common reasons for this below.

Situations When Index Usage Is Forsaken in MySQL Query Optimization

Full Table scan:

In some situations, the optimizer determines that conducting a full table scan is more efficient than utilizing an index. Let's illustrate this with an example:

Consider a scenario in the 'employees' database, focusing on the 'salaries' table. We have created an index called 'idx_salary' on the 'salary' column. Now, let's analyze a specific query:

 
Explain select * from salaries where salary > 50000\G

Output:

 
id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: idx_salary
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 815849
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

In this case, the idx_salary is created on the salary column. The explain plan examines 815,849 rows without using the index. The original result is 621,322 rows. Hence, it makes sense why opting for a full table scan is the better choice.

 
select count(*) from salaries where salary > 50000; 
+----------+
| count(*) |
+----------+
|   621322 |
+----------+

Cardinality and Its Impact on MySQL Query Optimization

When the cardinality of the column is less, the purpose of using an index may be nullified. Cardinality refers to the number of unique values in a column. When the cardinality of a column is low, it means that there are relatively few unique values, and many rows in the table share the same values in that column. In such cases, using an index on that column may be less beneficial for certain types of queries.

You can determine the cardinality of a column in two ways depending on whether an index exists or not.

Without an Existing Index: In cases where there's no existing index on a column, you can calculate the cardinality by using the COUNT (DISTINCT) statement. This method involves counting the number of unique values in the column.

Example:

 
SELECT COUNT(DISTINCT salary) AS cardinality FROM salaries;

With an Existing Index: If an index has already been created on the column, you can check its cardinality by executing the SHOW INDEX query. This query provides information about the index, including the number of unique entries it contains.

Example:

 
show index from salaries where Key_name = 'idx_salary';

Statistics and MySQL Query Optimization

The optimizer solely decides the indexes to be used based on the statistics collected and analysed. If the statistics are outdated or inaccurate, the optimizer may make suboptimal choices. You can update statistics to improve accuracy.

Index Obfuscation in MySQL Query Optimization

When we create a functional index, the existing index on the column where a functional index was created becomes obfuscated.

Let's take an example from our database to illustrate this:

Database: employees

Table: employees

Explanation:

Consider the following query:

 
Explain select emp_no, birth_date, first_name from employees where month(birth_date)=9\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 292025
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

In this case, the employees table has an index on the column birth_date. However, the optimizer obfuscates this index and chooses to use the functional index created for the month() function. The output of the query using the functional index is as follows:

 
//sample sql query with month() function//

Explain select emp_no, birth_date, first_name from employees where month(birth_date)=9\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: functional_index
          key: functional_index
      key_len: 5
          ref: const
         rows: 47370
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Limitations of MySQL Query Optimization

Optimizer's Index Selection:

Sometimes, the optimizer may not choose the most suitable index for your query.
In such cases, you can try to influence the optimizer's choice by using query hints.
However, it's important to note that even with hints, the optimizer might still opt for an alternative access method if it deems it more efficient.

Impact of Data Statistics:

Accurate and up-to-date statistics are crucial for the optimizer to make well-informed decisions.
When statistics are inaccurate or outdated, the optimizer may generate suboptimal execution plans, which can negatively affect query performance.

Small Tables and Index Usage:

In the case of exceptionally small tables, the performance improvement gained from using an index may not be significant.
In such situations, the optimizer may decide to perform a full table scan instead of utilizing an index.

Cost Model Influence:

The optimizer relies on a cost model that considers various factors when assessing query execution plans.
However, this cost model may not always perfectly reflect real-world performance, leading to instances where the chosen plan doesn't align with the expected outcomes.

Join Operations and Indexes:

In complex queries that involve multiple tables and join operations, the optimizer might select join algorithms that do not benefit from the use of indexes.
This can result in different execution strategies for such queries.

Missing or Poorly Designed Indexes:

When columns involved in the WHERE clause lack proper indexes or if the existing indexes are not well-designed, the optimizer may have no choice but to perform full table scans.
This can have a significant impact on query efficiency and overall performance.

Mastering MySQL query optimization requires a deep understanding of these concepts, as well as practical knowledge of query hints to influence the optimizer's index selection. Additionally, ensuring that your data statistics are accurate and up-to-date is crucial for optimal performance.

In the world of MySQL query optimization, knowing when to rely on indexes and when to opt for other methods is key to achieving efficient and lightning-fast database operations. With these insights and practical tips, you can navigate the intriguing world of MySQL query optimization with confidence and keep your databases running at their best.

Reference 1

Reference 2

Reference 3

Stay connected with Mydbops Blogs for more technical insights and discoveries in the MySQL ecosystem.