SQL Optimization in Action: Query 10x Slower, Do You Check Indexes or the Execution Plan First?

When facing sudden slow query alerts in production, most backend engineers' immediate reaction is to check WHERE clauses and hastily add indexes. However, this "knee-jerk" optimization often backfires—response times on monitoring dashboards may not drop, but rather worsen due to additional index maintenance overhead. This reveals a brutal technical truth: the intent of index design does not equate to the database's actual execution. The moment an SQL statement is submitted to the database engine, it enters a complex decision-making process governed by the Query Optimizer. Most modern databases employ Cost-Based Optimization (CBO); in the optimizer's precise calculations, the sequential I/O cost of a full table scan is sometimes far lower than the cost of extensive random I/O table lookups via secondary indexes. Consequently, when data distribution is uneven, field cardinality is low, or statistics are outdated, the database will unhesitatingly discard carefully designed indexes in favor of a seemingly cumbersome full table scan. True SQL tuning is never a simple game of "fill in the blanks," but a trade-off based on data characteristics and execution costs. To break the "index failure" puzzle, developers must stop speculating on index behavior and master the core tool for visualizing the database's decision process: the execution plan. Only by deeply understanding the hierarchy of type in EXPLAIN output, accurately interpreting the I/O implications of rows estimates, and keenly identifying key signals like Using filesort or covering indexes, can engineers fundamentally pinpoint performance bottlenecks. This transforms uncontrollable "voodoo" optimization into quantifiable engineering practice, ensuring every architectural adjustment delivers a tangible performance leap.

Why Is the Query Still Slow With an Index? Finding Answers in the Execution Plan

The vast majority of backend engineers have experienced such a "darkest moment": a production alert shows a SQL query timeout, you quickly investigate the code, and find that the WHERE condition field has no index. So, you confidently submit an ALTER TABLE ADD INDEX, thinking the problem is solved instantly. However, after deployment, the monitoring curve remains motionless, and sometimes the query even becomes slower.

This frustration stems from a common misconception: assuming that as long as an index is created, the database will definitely use it.

In fact, between sending a SQL statement to the database engine (such as MySQL InnoDB) and the actual execution of data retrieval, there exists a crucial decision-making layer—the Query Optimizer. For modern relational databases, the vast majority use a Cost-Based Optimizer (CBO).

The Optimizer's "Cost" Bill

When you execute a SQL statement, the optimizer does not blindly use an index just because it sees one. Its core responsibility is to calculate the "Cost" of various execution paths and select the one with the lowest cost.

The so-called "cost" usually consists of I/O cost (the number of times data pages are read from disk) and CPU cost (record comparison, sorting, temporary table operations, etc.).

• Full Table Scan: Although it sounds clunky, it is sequential I/O. If your query needs to access 80% of the data in the table, sequential reading is often much faster than random I/O via an index (table lookup operations).
• Index Scan: Although precise in positioning, if the index cannot cover all query fields, frequent table lookups are required. When data distribution is uneven or statistics are outdated, the optimizer may determine that "using the index is more expensive than a full table scan," thereby discarding your carefully designed index.

As mentioned in the discussion about full table scans on Hacker News, indexes can sometimes bring about a performance "cliff"—when a slight change in data volume or distribution causes the execution plan to change, performance may drop instantly, whereas the performance decay of a full table scan is usually linear.

Intent vs. Reality

This leads to the core cognitive gap in SQL optimization:

• Index Design is your "Intent": You hope the database looks up data following a certain path.
• Execution Plan is the "Reality": The database ultimately decides how to look up data.

The bridge between the two is statistics. If statistics show that the cardinality of a column is extremely low, or the data distribution is severely skewed, the optimizer will ignore your intent.

Therefore, when setting out to optimize slow SQL, guessing ("I think an index should be added here") is extremely dangerous. The only source of truth is the execution plan told to you by the database.

The following chapters will take MySQL as an example (its logic also applies to relational databases like PostgreSQL, Oracle, etc.) to deeply interpret how to inspect the optimizer's decision-making process through the EXPLAIN command and find the real reason for "index failure." We will no longer focus on the theoretical "what is an index," but focus on solving the "why wasn't it used" in real-world engineering scenarios.

Understanding EXPLAIN: Key Metrics and "Traffic Light" Signals

On the battlefield of database performance optimization, the EXPLAIN command is your tactical map. It won't directly tell you "how to modify the SQL," but it will honestly show how the database optimizer "thinks" and executes your query.

To obtain the execution plan of a query, simply add the EXPLAIN keyword before the SELECT statement. For complex queries, you don't need to memorize the definition of every column in the output, but you must be able to identify the key metrics that determine performance life or death at a glance.

The following is a typical EXPLAIN output structure:

EXPLAIN SELECT * FROM orders WHERE user_id = 1024 AND status = 'paid';

Amidst this seemingly boring data, you need to prioritize four core metrics, which constitute the "dashboard" of performance analysis:

• type: Access type, determining whether the query is a "full table scan" or a "precision strike."
• key: The index actually used. If this is NULL, it means the query is "running naked."
• rows: Estimated number of rows to scan.
• Extra: Extra information, often containing "subtext" regarding index usage efficiency.

Watch Out for rows: The Direct Indicator of I/O Cost

When viewing the execution plan, many developers tend to overlook rows and obsess over index names. In reality, rows is the most intuitive quantitative metric for measuring I/O cost.

This column represents the number of rows the optimizer estimates must be read to find the target data. Please note that this is an estimate and is not always equal to the size of the result set.

• If the rows value is huge (e.g., tens or hundreds of thousands), even if the key column shows an index is used, the query may still cause significant disk I/O, which usually implies poor index Selectivity.
• According to Alibaba Cloud's deep dive, the filtered column (displayed by default in newer versions of MySQL) can assist rows in making judgments: it represents the percentage of rows returned in the result set relative to the number of rows read; a higher value indicates more precise index filtering.

Once you confirm that rows is within a reasonable range, the next step is to deeply interpret type and Extra. These two fields will tell you whether the database is utilizing the index efficiently or performing inefficient "fake moves."

Core Field Interpretation: The Subtext of Type and Extra

In the output results of EXPLAIN, rows tells you how much work the database "estimates" it needs to do, while type and Extra reveal "how" it intends to do it. These two fields often directly determine whether a query responds in milliseconds or causes the database to stall.

1. Type: The "Hierarchy" of Access Efficiency

The type field represents the way MySQL looks up data rows (Access Method). The key to understanding this field lies in remembering this ranking sequence from best to worst:

1. system > const

• • Scenario: Equality queries based on primary keys or unique indexes (e.g., WHERE id = 1).
  • Significance: Returns at most one row of data; extremely fast, can be understood as constant time complexity.

1. eq_ref

• • Scenario: When performing multi-table joins, for each row in the previous table, the subsequent table can find only one unique matching row via the primary key or unique index.
  • Significance: This is the most ideal join type in Join queries.

1. ref

• • Scenario: Using a non-unique index, or the leftmost prefix of a unique index for lookup.
  • Significance: Although the index is not unique and will return all rows matching a certain value, it is still an efficient index lookup.

1. range

• • Scenario: Index range scan, commonly seen in BETWEEN, >, <, or IN operations.
  • Significance: Better than a full index scan because it only scans a specific interval of the index tree.

1. index (Full Index Scan)

• • Scenario: Scans and traverses the entire index tree.
  • Significance: Although faster than a full table scan (because index files are usually smaller and more compact than data files), this usually means the index is not being utilized efficiently (e.g., missing the leftmost prefix), or the query needs to scan a large number of index records.

1. ALL (Full Table Scan)

• • Scenario: Full table scan.
  • Significance: Red Alert. Unless the table is extremely small (like a configuration table), the appearance of ALL in a table with tens of thousands of records or more usually means immediate optimization is required.

2. Extra: Not Just a Note, But a Critical Verdict

Many developers easily confuse similar terms appearing in the Extra field. In reality, this implies the specific location where data filtering occurs (Server layer or Storage Engine layer), and the difference is huge:

Practical Tip:
If you see Using where; Using index simultaneously in Extra, this is usually good news, indicating that although WHERE filtering is used, the data still comes entirely from the index, without generating expensive table access operations. Conversely, if there is only Using where and type is ALL, it is a typical inefficient query.

Cheat Sheet: "Red Flags" and "Green Lights" in Execution Plans

When analyzing slow query logs, the type and Extra fields in the EXPLAIN output are core indicators for judging query efficiency. The following cheat sheet can help you quickly identify health signals and potential risks in execution plans, making it especially suitable as a "first aid guide" when troubleshooting urgent issues.

Deep Dive: Why is Using filesort a Performance Killer?

In the perception of many developers, sorting seems as simple as "sorting in memory," but at the database level, Using filesort often signifies a failure in index design.

As pointed out in the MySQL official documentation on ORDER BY Optimization, when data cannot be read directly in order using an index, MySQL must execute an additional sorting phase. The cost of this process lies in:

1. CPU and Memory Pressure: The database needs to allocate a sort_buffer to store row pointers and sort keys. If the result set exceeds the buffer size, the sorting operation will spill over to disk, causing severe random I/O.
2. Forfeiting Pre-sorting Advantages: B+ tree indexes are inherently ordered. If the index is designed properly (e.g., satisfying the leftmost prefix rule), the database can read data directly in index order, completely skipping the sorting step.

Practical Advice: When you see Using filesort, do not just check if the ORDER BY field has an index; also confirm whether the fields in the WHERE clause can form a composite index with the sorting field, thereby utilizing the ordered nature of the index to eliminate extra sorting overhead.

Index Failure and Full Table Scans: Why the Optimizer "Betrays" You?

Many developers have experienced this moment of "betrayal": you clearly built an index on the status field, but when executing SELECT * FROM orders WHERE status = 1, the type in the Explain result still shows ALL (Full Table Scan). You might suspect that statistics haven't been updated, or that MySQL has a bug.

In reality, the database Optimizer is extremely rational; its core decision metric is just one thing: Cost. The optimizer does not blindly believe in indexes; it only chooses the path with the lowest cost. When it "betrays" your carefully designed index, it is usually because it has calculated that a full table scan is more cost-effective than using the index.

Selectivity and I/O Cost

The key to understanding this problem lies in the trade-off between Random I/O and Sequential I/O.

An index query usually involves two steps:

1. Index Seek: Finding the corresponding primary key ID in the index tree.
2. Table Lookup: Reading the complete row data from the clustered index (main data file) based on the ID.

This process involves a large amount of Random I/O. If your query condition hits a large portion of the data in the table (e.g., more than 30% of the total rows), the overhead of random I/O generated by "jumping reads" through the index is often far higher than the overhead of directly sequentially scanning (Sequential Scan) the entire table.

As pointed out in the MySQL Official Documentation: "When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all rows are needed for the query."

Therefore, selectivity is the prerequisite for an index to be effective. If a field (such as gender or status) has very few unique values and the distribution is extremely uneven, then on the side with the larger amount of data, the index will often fail.

Specific "Betrayal" Scenarios: The Cost of SELECT *

Often, the culprit behind index failure is the greedy SELECT *.

Suppose there is a query:

SELECT * FROM users WHERE age > 30;

If the age field has an index, but you need to query all columns (*), the optimizer must perform a table lookup operation for every record that satisfies age > 30. If the number of rows satisfying the condition is large, the cost of table lookups will be extremely high, and the optimizer will decisively abandon the age index in favor of a full table scan.

Comparative Test:
If you change the SQL to SELECT id FROM users WHERE age > 30, you will find that the execution plan instantly turns green (Using index). This is because id is right on the index tree (Covering Index), eliminating the need for table lookups, so the optimizer is naturally happy to use the index.

Common Implicit Index Failure Traps

Besides abandonment based on cost, there is another situation where our SQL syntax directly destroys the search characteristics of the B+ tree, causing the index to be unusable. Here are three of the most common "anti-patterns":

1. Using Functions on Fields

    -- Error: Index fails because every row must be calculated
    SELECT  FROM users WHERE YEAR(create_time) = 2023;

-- Correct: Convert to range query
    SELECT  FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

Once a calculation or function operation is performed on an index column, the database must traverse all rows to calculate the result, and the ordered nature of the index instantly loses its meaning.

1. Implicit Type Conversion
   If the phone field is of VARCHAR type, but a number is used in the query:

    -- Error: String is implicitly converted to number for comparison
    SELECT  FROM users WHERE phone = 13800138000;

-- Correct: Keep types consistent
    SELECT  FROM users WHERE phone = '13800138000';

This implicit conversion is equivalent to adding a conversion function to the field, directly leading to a full table scan.

1. Leading Fuzzy Query

    -- Error: B+ tree cannot start matching from the middle
    SELECT * FROM products WHERE name LIKE '%Pro';

LIKE 'Pro%' can use a range index, but LIKE '%Pro' means that every character from start to finish could be a match; the ordered nature of the index cannot be utilized, leaving only a full table scan.

After understanding these mechanisms, when you encounter ALL again, do not rush to force a Hint. First check the data distribution (whether too much data is being fetched), then check the SQL syntax (whether it violates index rules). As mentioned in Alibaba Cloud's technical analysis, even operations like IS NULL or IS NOT NULL depend entirely on the specific data distribution for whether they use an index, rather than an immutable rule.

Real-world Tuning Case Study: From Problem Discovery to Resolution

The ultimate goal of theoretical knowledge is real-world practice in a production environment. In this section, we will build a reproducible "slow query" scenario to simulate a complete tuning process, from discovering performance bottlenecks and analyzing execution plans to finally resolving the issue. It is recommended that you follow these steps in your local database for experimentation, or compare them with your own Slow Query Log.

We will focus on the changes in two metrics: scanned rows (rows) and extra operations (Extra).

Scenario Reproduction: A Typical "Death by Sorting" Case

Suppose we have an e-commerce order table orders with a data volume of about 5 million rows. The business side reports that when querying the recent orders of a certain user, the interface response time sometimes exceeds 2 seconds.

Table Structure and Initial Index:

CREATE TABLE orders (
  id INT PRIMARY KEY AUTOINCREMENT,
  userid INT NOT NULL,
  status TINYINT NOT NULL, -- 1: Pending, 2: Paid, 3: Shipped
  amount DECIMAL(10, 2),
  createtime DATETIME,
  KEY idxuser (userid)  -- Initially established a single-column index on userid
) ENGINE=InnoDB;

Problematic SQL:

SELECT id, status, amount 
FROM orders 
WHERE userid = 10086 
ORDER BY createtime DESC 
LIMIT 20;

This is a very high-frequency business query pattern: filter by user first, then paginate in reverse chronological order.

Step 1: Analyze the "Accident Scene" (Before)

Run EXPLAIN directly to view the current execution plan:

EXPLAIN SELECT id, status, amount FROM orders WHERE userid = 10086 ORDER BY createtime DESC LIMIT 20;

Output Analysis:

Interpreting Red Flags:

1. type: ref: Looks good, used the idx_user index.
2. rows: 25000: The optimizer estimates that this user has 25,000 orders. MySQL must first find these 25,000 records.
3. Extra: Using filesort: This is a performance killer. Although we quickly located the data via idx_user, the index itself is not sorted by create_time. Therefore, MySQL must load these 25,000 records into memory (sort buffer) for sorting. If the data volume exceeds sortbuffersize, it will also lead to disk temporary file sorting, causing severe I/O jitter.

As pointed out in Pythian's technical blog, as the dataset grows, any filesort operation can cause performance to fail to scale linearly, and eliminating it is the primary task of optimization.

Step 2: Implement Optimization Strategy (Action)

To eliminate filesort, we need to leverage the ordered nature of B+ trees and establish a composite index that satisfies both "filtering" and "sorting".

Modify Index:

-- Create composite index (userid, createtime)
ALTER TABLE orders ADD INDEX idxusertime (userid, createtime);

Step 3: Verify Optimization Results (After)

Run EXPLAIN again:

EXPLAIN SELECT id, status, amount FROM orders WHERE userid = 10086 ORDER BY createtime DESC LIMIT 20;

Output Comparison:

Interpretation of Optimization Results:

1. key: idxusertime: MySQL chose the new composite index.
2. rows: 20: The number of scanned rows plummeted from 25,000 to 20. Since the index is already sorted by userid + createtime, MySQL only needs to directly read the last 20 records to satisfy the query, without scanning all historical orders for that user.
3. Extra changed to Backward index scan (MySQL 8.0+): Completely eliminated Using filesort. This means query response time will drop from hundreds of milliseconds to microseconds.

Advanced Troubleshooting: When the Optimizer "Disobeys"

In practice, you may encounter situations where MySQL still insists on a full table scan or uses the wrong index even after creating an index. This is usually related to Selectivity and Cost.

For example, if we change the query to query "all paid orders":

SELECT * FROM orders WHERE status = 2 ORDER BY create_time;

Even if you create an index on (status, create_time), if the data with status=2 (Paid) accounts for 80% of the table, the optimizer will likely abandon the index. Because it judges that the cost of finding the primary key through the secondary index and then looking up the data (Random I/O) is higher than directly scanning the full table sequentially (Sequential I/O).

Debugging Tips:
In this situation, you can use FORCE INDEX as a diagnostic tool (but use it with caution in production code).

SELECT * FROM orders FORCE INDEX(idxstatustime) WHERE status = 2 ORDER BY create_time;

By forcibly specifying the index and comparing the rows in EXPLAIN with the actual execution time (Profiling), you can confirm whether the issue is due to index design or biased statistics (Statistics). As mentioned in the Stack Overflow discussion, although FORCE INDEX is a last resort, it is the most direct way to verify index effectiveness during the debugging phase.

Summary:
Do not just look at the query results; you must look at EXPLAIN. The core goals of optimization are usually:

1. Promote type from ALL to ref or range.
2. Eliminate Using filesort and Using temporary in Extra.
3. Significantly reduce the number of scanned rows.

Case 1: Eliminating Using filesort (Sorting Optimization)

When dealing with slow queries, Using filesort is one of the most common performance killers in execution plans. It does not necessarily imply disk I/O (although it occurs when memory is insufficient), but it explicitly indicates that MySQL cannot use an index to complete the sorting and must perform an extra sorting operation after retrieving the data. This usually leads to soaring CPU usage and extended response times.

Scenario Reproduction

Suppose we have an e-commerce order table orders, and the business side needs to query the recent orders of a specific user. The table structure contains a single-column index idxuserid (user_id).

-- Original query: Find orders for user 10086, sorted by creation time in descending order
SELECT id, orderno, amount, createdat 
FROM orders 
WHERE userid = 10086 
ORDER BY createdat DESC 
LIMIT 10;

When we execute EXPLAIN to view the execution plan of this SQL, we usually see the following results:

Problem Analysis:
Although the query used idxuserid to quickly locate the rows where user_id = 10086, the index itself is sorted by user_id. When user_id is the same, the data is not guaranteed to be ordered by created_at in physical storage or the index tree. Therefore, MySQL must extract all matching rows, put them into the sort buffer (Sort Buffer), and perform a secondary sort based on created_at. As stated in the MySQL Official Documentation, this constitutes an extra sorting phase, which may even trigger disk temporary file swapping when the data volume is large.

Optimization Solution: Composite Index and Leftmost Prefix

To eliminate Using filesort, the core idea is to make the index order directly satisfy the query's sorting requirements. We need to create a composite index, following the principle of "equality query fields first, sorting fields last".

-- Create composite index (A, B)
ALTER TABLE orders ADD INDEX idxusercreated (userid, createdat);

In the B+ tree structure of this composite index (userid, createdat):

1. The data is strictly sorted by user_id first.
2. When user_id is equal, the data is strictly sorted by created_at.

According to the Leftmost Prefix principle, when we specify user_id as a constant in the WHERE clause, the scanned index fragment itself is already ordered by created_at.

Verifying Optimization Effects

Execute EXPLAIN again:

EXPLAIN SELECT id, orderno, amount, createdat 
FROM orders 
WHERE userid = 10086 
ORDER BY createdat DESC 
LIMIT 10;

TABLEBLOCK6
(Note: Some versions may display Backward index scan, depending on the MySQL version and DESC optimization, but Using filesort has disappeared)

Result Interpretation:
Using filesort in the Extra field has disappeared. The executor can now read the first 10 records directly from the index tree in order and return them, without needing to scan all orders for that user and then sort them. For high-concurrency list page queries, this optimization usually brings a performance improvement of more than 10 times.

Note: If user_id in the WHERE clause is a range query (e.g., user_id > 1000), the optimizer usually cannot use the index to eliminate sorting even if a composite index is established, because created_at is not globally ordered when spanning multiple user_ids.

Case 2: Leveraging Covering Indexes to Avoid "Table Lookups"

In high-concurrency scenarios, the core of SQL optimization is often not "how to find rows faster," but "how to reduce I/O operations." Among them, "Table Lookups" (Table Access by RowID) are a common hidden culprit causing query performance jitter.

What is a "Table Lookup"?

When the database finds records matching conditions via a Secondary Index, if the leaf nodes of the index do not contain all the columns required by the query, the storage engine must use the Primary Key ID stored in the index to go back to the Clustered Index (main table data) to retrieve the complete row data.

This process is known as a "table lookup" (or "回表"). In the HDD era, this usually meant generating a large amount of random I/O; even with SSDs, frequent table lookups significantly increase CPU consumption and latency.

Scenario Reproduction: The Cost of SELECT *

Suppose we have an orders table containing tens of millions of records, with a composite index idxuserdate (userid, createdat).

Scenario A: Habitual Full-Field Query

Many developers, for the sake of convenience, habitually write SELECT *:

SELECT * FROM orders WHERE user_id = 10086;

At this point, checking the execution plan (EXPLAIN):

Although type is ref, indicating the index was used, Extra shows Using index condition (or is empty). This means that after MySQL filters out records where user_id = 10086 on the index tree, it must take the Primary Key ID back to the main table to read other fields like order_status and amount. If the user has 50 orders, this could trigger 50 random I/O operations.

Scenario B: Covering Index Optimization

If we only query the fields actually needed by the business logic, and these fields happen to be in the index:

SELECT userid, createdat FROM orders WHERE user_id = 10086;

Checking the execution plan again:

Now the Extra field has changed to Using index. This represents that MySQL retrieved all necessary data just by scanning the idxuserdate index tree, completely skipping the step of accessing main table data.

According to PlanetScale's EXPLAIN guide, Using index explicitly indicates that MySQL will use a covering index to avoid accessing the table. This query method transforms the original "index scan + random I/O table lookup" into a pure "sequential index scan," and the performance improvement is usually by orders of magnitude.

Optimization Strategies

In practice, you can eliminate table lookups by leveraging covering indexes in the following two ways:

1. Subtraction (Modify SQL): Strictly review query fields, remove useless SELECT *, and only select columns already present in the index.
2. Addition (Modify Index): If the business logic indeed requires querying order_status, and this query is a high-frequency core path, consider extending the composite index to (userid, createdat, order_status).

Although increasing index length slightly affects insertion performance, for read-heavy/write-light scenarios, trading a small amount of space for a massive reduction in random I/O is usually a highly cost-effective optimization method. When analyzing with EXPLAIN, be sure to pay attention to whether Using index appears in the Extra column; this is the "gold standard" for determining whether a table lookup occurs.

Case 3: Is Force Index a Lifesaver?

In rare cases, you might encounter a maddening scenario: you have created a perfect index for the query fields, and the data distribution seems normal, but the EXPLAIN result shows that the optimizer insists on using a Full Table Scan or selects a completely irrelevant index.

At this point, the first reaction of many developers is to use FORCE INDEX to forcibly correct the optimizer's behavior. Although this can solve the current slow query problem immediately, it is often a double-edged sword in production environments.

Why Does the Optimizer "Act Foolishly"?

The database optimizer makes decisions based on cost. It relies on statistics to estimate the number of scanned rows and I/O costs. If the statistics are outdated—for example, if a large amount of data has just been written or deleted, and the background statistical analysis has not yet been triggered—the data distribution in the eyes of the optimizer may be vastly different from the actual situation.

In addition, MySQL's indexing strategy evaluates the cost of table lookups. If the query condition matches a large proportion of data (usually exceeding 20%-30% of the total table rows), the optimizer will consider the cost of looking up via a secondary index and then accessing the table (Random I/O) to be higher than a direct sequential scan of the full table (Sequential I/O), thus abandoning the index.

Usage and Risks of Force Index

FORCE INDEX is indeed the strongest measure to intervene in the execution plan. By specifying the index name after the table name, you can command MySQL to follow this path.

Syntax Example:

-- The optimizer chose a full table scan, but you are sure only 1% of the data is queried
SELECT * 
FROM orders FORCE INDEX (idxorderdate)
WHERE order_date >= '2023-11-01';

Although this looks like a "lifesaver," in the eyes of senior DBAs, hardcoding FORCE INDEX in business code is usually considered an Anti-pattern, for the following reasons:

1. Brittleness: Hardcoding the index name in SQL means that if the database is refactored later (such as renaming or merging indexes), the application code will directly report an error or fail.
2. Risk of Changing Data Distribution: Over time, data distribution may change. If one day the data volume filtered by order_date >= '...' becomes 50%, a full table scan is indeed the better solution. However, because the index is forced in the code, the database has to execute a large amount of extremely inefficient random I/O table lookups, causing the original optimization measure to become a performance bottleneck.
3. Masking Root Causes: Frequent need for forced indexes usually implies that the statistics maintenance mechanism has failed, or that the index design itself is flawed (for example, not covering all necessary fields).

Correct Troubleshooting and Handling Path

When you find that the optimizer has chosen the wrong index, it is recommended to handle it according to the following priority, rather than directly deploying FORCE INDEX:

1. Update Statistics:
   This is the most common reason. Try executing ANALYZE TABLE table_name; in a test environment or during off-peak hours. This prompts the database to resample and update the index cardinality statistics. In many real-world cases, this step alone can make the optimizer "regain its sanity."
2. Optimize SQL Syntax or Index Structure:
   Check if using SELECT * causes excessive table lookup costs. Try rewriting it as a covering index query, or use STRAIGHT_JOIN to adjust the table join order, guiding the optimizer to naturally select the correct path.
3. Last Resort:
   If updating statistics is ineffective and business logic requires immediate performance recovery, you can use FORCE INDEX as a temporary fix. However, be sure to mark this as a temporary measure (TODO) in the code comments and schedule follow-up tasks to deeply analyze why the optimizer's estimation failed.

Expert Advice: Do not blindly trust that your intuition is better than the optimizer. Unless you have solid evidence (such as a comparison of actual execution times from EXPLAIN ANALYZE) showing that an index scan is significantly better than the optimizer's choice, try to avoid manually intervening in the execution plan.

Conclusion: Establishing a "Design-Verify-Tune" Closed-Loop Mindset

Returning to the question at the beginning of the article: "The query slowed down by 10 times; do you check the index first or the execution plan first?"

Actually, this is a typical trick question. In mature engineering practice, Index and Execution Plan are never an isolated "either-or" choice, but a closed-loop system running through the SQL lifecycle. Relying solely on index design while ignoring execution plan verification, or only checking the execution plan when a failure occurs, is like "walking on one leg."

To thoroughly solve slow query problems, we need to establish a complete "Design-Verify-Tune" workflow:

1. Design Phase: Business-Driven Rather Than "Shotgun" Indexing

Optimization begins before code is written. Do not wait until the testing phase to consider indexes, and do not add indexes to all fields just to "prevent trouble before it happens."

• Precision Strike: Design indexes based on high-frequency WHERE, JOIN, and ORDER BY conditions in business queries.
• Weigh the Costs: Remember the balance mentioned in Klaviyo's engineering practice: every index slows down write speed. Write performance degradation caused by over-indexing is often harder to troubleshoot than slow queries.
• Composite Index Order: Strictly follow the "leftmost prefix" principle and place the fields with the highest selectivity at the front.

2. Verification Phase: "Red Line" Checks in the Development Environment

Many serious performance incidents could have been avoided in the development phase with a simple EXPLAIN. Do not just run SQL in a local database with only 10 rows of data; you must introduce an execution plan review mechanism into the development process.
Pay attention to the following "red line" indicators:

• Type Check: Be alert when seeing ALL (full table scan), unless the table data volume is extremely small. Ideally, core queries should reach the ref or range level.
• Extra Warnings: Using filesort and Using temporary mean the database is consuming significant CPU and memory resources for extra calculations, which are performance killers under high concurrency.
• Row Estimation: As shown in Rapydo's benchmark, a missing index can cause the database to scan 1 million rows instead of 10, resulting in a performance gap of up to 3000 times.

3. Tuning Phase: Embracing Change and Safe Trial-and-Error

Data distribution in the production environment is dynamic, and the optimizer may choose the wrong index due to outdated statistics.

• Continuous Monitoring: Use the Slow Query Log to locate those SQLs that were "originally fast but suddenly became slow."
• Safe Verification: MySQL 8.0+ introduced Invisible Indexes, which changed the traditional operations risk model. You can first mark an index as "invisible," observe changes in the execution plan and performance, and then physically delete it after confirmation, thus avoiding the risk of "service avalanche caused by deleting the wrong index."

Final Advice for Engineers:
Never blindly trust the theoretical "index hit." Before submitting code, try to construct test data of the same order of magnitude as the production environment locally (for example, using stored procedures to generate 500,000 rows of data), and then run EXPLAIN. Only when you see with your own eyes that the rows field has changed from 500,000 to 1 can you be confident that this line of code is safe in the production environment.