Why the Same SQL Has Different Execution Plans in QA and DEV Environments and How to Optimize It

1. Problem Description

Developers reported that the same SQL statement takes about 0.1 s in the QA environment but 0.3‑0.5 s in the DEV environment. The original query selects many columns from CMDB_PHYSICAL_MACHINE and joins several lookup tables.

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px"><span style="color: #c678dd; line-height: 26px">SELECT</span> machine.id,
       machine.asset_number,
       machine.sn,
       ...
       machine.public_ip,
       machine.other_ips,
       machine.create_time,
       machine.update_time,
       machine.creator,
       machine.updater,
       machine.delete_flag,
       machine.disk_desc_id,
       res.id res_id,
       ...
 <span style="color: #c678dd; line-height: 26px">FROM</span> CMDB_PHYSICAL_MACHINE machine
 <span style="color: #c678dd; line-height: 26px">LEFT</span> <span style="color: #c678dd; line-height: 26px">JOIN</span> cmdb_dropdown_list cdl1 ON (machine.check_hardware=cdl1.code and cdl1.type="HardwareCheck")
 ...
 <span style="color: #c678dd; line-height: 26px">WHERE</span> 1=1
   <span style="color: #c678dd; line-height: 26px">AND</span> machine.delete_flag=0
 <span style="color: #c678dd; line-height: 26px">ORDER BY</span> id <span style="color: #c678dd; line-height: 26px">DESC</span>
 <span style="color: #c678dd; line-height: 26px">LIMIT</span> 0,30</code>

2. Analysis

Examining the execution plans revealed that the two environments use different join orders and algorithms.

2.1 Execution plan in QA

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px">+----+-------------+---------+------------+--------+-----------------------------+---------------+---------+----------------------------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys               | key           | key_len | ref                              | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+-----------------------------+---------------+---------+----------------------------------+------+----------+-------------+
|  1 | SIMPLE      | machine | NULL       | index  | NULL                        | PRIMARY       | 4       | NULL                             |    1 |   10.00 | Using where |
|  1 | SIMPLE      | cdl1    | NULL       | ref    | idx_type_code               | idx_type_code | 62      | const                            |    3 |  100.00 | Using where |
|  1 | SIMPLE      | cdl2    | NULL       | ref    | idx_type_code               | idx_type_code | 62      | const                            |    9 |  100.00 | Using where |
|  1 | SIMPLE      | ci      | NULL       | eq_ref | PRIMARY                     | PRIMARY       | 4       | omms.machine.idc_id             |    1 |   10.00 | Using where |
... (other joins omitted for brevity) ...
+----+-------------+---------+------------+--------+-----------------------------+---------------+---------+----------------------------------+------+----------+-------------+
12 rows in set, 1 warning (0.01 sec)</code>

The optimizer chose machine as the driving table and used an Index Nested Loop, avoiding a filesort.

2.2 Execution plan in DEV

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px">+----+-------------+---------+------------+--------+-----------------------------+---------------+---------+----------------------------------+------+----------+----------------------------------------------------+
| id | select_type | table   | partitions | type   | possible_keys               | key           | key_len | ref                              | rows | filtered | Extra                                            |
+----+-------------+---------+------------+--------+-----------------------------+---------------+---------+----------------------------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | ci      | NULL       | ALL    | PRIMARY                     | NULL          | NULL    | NULL                             |    8 |   12.50 | Using where; Using temporary; Using filesort      |
|  1 | SIMPLE      | machine | NULL       | ALL    | NULL                        | NULL          | NULL    | NULL                             | 1976 |    1.00 | Using where; Using join buffer (Block Nested Loop) |
... (other joins omitted) ...
+----+-------------+---------+------------+--------+-----------------------------+---------------+---------+----------------------------------+------+----------+----------------------------------------------------+
12 rows in set, 1 warning (0.00 sec)</code>

Here the optimizer chose ci as the driving table; because machine.idc_id lacks an index, it fell back to a Block Nested Loop and required a filesort, which caused the slowdown.

2.3 Checking tables, indexes, and data distribution

The schema, indexes and data distribution are essentially identical between the two environments.

2.4 Re‑collecting statistics

Running ANALYZE TABLE on machine and ci in DEV did not change the plan.

2.5 Database version differences

QA runs MySQL 5.7.34, DEV runs 5.7.25. The optimizer_switch variable shows an extra option in 5.7.34: prefer_ordering_index=on. The official description is:

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px">Controls whether, in the case of a query having an ORDER BY or GROUP BY with a LIMIT clause, the optimizer tries to use an ordered index instead of an unordered index, a filesort, or some other optimization. ...
</code>

Disabling this flag does not affect the observed plan, and upgrading DEV to 5.7.34 still reproduces the same behavior, so the version difference is not the root cause.

2.6 Using STRAIGHT_JOIN to force the driver table

Adding the hint STRAIGHT_JOIN forces machine to be the driver table, which eliminates the filesort:

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px"><span style="color: #c678dd; line-height: 26px">SELECT</span> ...
 <span style="color: #c678dd; line-height: 26px">STRAIGHT_JOIN</span> cmdb_idc ci ON (machine.idc_id=ci.id and ci.delete_flag=0)
 ...
 <span style="color: #c678dd; line-height: 26px">ORDER BY</span> id <span style="color: #c678dd; line-height: 26px">DESC</span>
 <span style="color: #c678dd; line-height: 26px">LIMIT</span> 0,30</code>

The plan now uses machine as the driver, but relying on a hint is not an elegant solution.

2.7 Rewriting the SQL

Removing unrelated LEFT JOIN tables simplifies the statement:

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px"><span style="color: #c678dd; line-height: 26px">SELECT</span> *
<span style="color: #c678dd; line-height: 26px">FROM</span> CMDB_PHYSICAL_MACHINE machine
<span style="color: #c678dd; line-height: 26px">JOIN</span> cmdb_idc ci ON (machine.idc_id=ci.id and ci.delete_flag=0)
<span style="color: #c678dd; line-height: 26px">WHERE</span> 1=1
<span style="color: #c678dd; line-height: 26px">AND</span> machine.delete_flag=0
<span style="color: #c678dd; line-height: 26px">ORDER BY</span> machine.id <span style="color: #c678dd; line-height: 26px">DESC</span>
<span style="color: #c678dd; line-height: 26px">LIMIT</span> 0,30;</code>

Now both QA and DEV produce identical plans (both using a Block Nested Loop because ci.delete_flag=0 forces ci to be the driver).

2.8 Removing the unnecessary condition

The predicate ci.delete_flag=0 is redundant—valid machines must belong to a valid IDC. Dropping it lets the optimizer choose machine as the driver, yielding a stable, index‑driven plan without sorting:

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menno, monospace; font-size: 12px"><span style="color: #c678dd; line-height: 26px">EXPLAIN SELECT *</span>
<span style="color: #c678dd; line-height: 26px">FROM</span> CMDB_PHYSICAL_MACHINE machine
<span style="color: #c678dd; line-height: 26px">JOIN</span> cmdb_idc ci ON (machine.idc_id=ci.id)
<span style="color: #c678dd; line-height: 26px">WHERE</span> 1=1
<span style="color: #c678dd; line-height: 26px">AND</span> machine.delete_flag=0
<span style="color: #c678dd; line-height: 26px">ORDER BY</span> machine.id <span style="color: #c678dd; line-height: 26px">DESC</span>
<span style="color: #c678dd; line-height: 26px">LIMIT</span> 0,30;</code>

The resulting plan shows machine using its primary key (index) and ci accessed via the primary key, with no filesort.

3. Summary

When writing SQL, understand which execution plan is optimal; avoid unnecessary predicates that mislead the optimizer, consider using scalar sub‑queries or removing irrelevant joins, and let the optimizer choose the most efficient driving table to keep the plan simple and stable.