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.

SELECT
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,
       ...
FROM
CMDB_PHYSICAL_MACHINE machine
LEFT
JOIN
cmdb_dropdown_list cdl1 ON (machine.check_hardware=cdl1.code and cdl1.type="HardwareCheck")
 ...
WHERE
1=1
AND
machine.delete_flag=0
ORDER BY
id
DESC
LIMIT
0,30

2. Analysis

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

2.1 Execution plan in QA

+----+-------------+---------+------------+--------+-----------------------------+---------------+---------+----------------------------------+------+----------+-------------+
| 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)

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

2.2 Execution plan in DEV

+----+-------------+---------+------------+--------+-----------------------------+---------------+---------+----------------------------------+------+----------+----------------------------------------------------+
| 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)

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:

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. ...

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:

SELECT
...
STRAIGHT_JOIN
cmdb_idc ci ON (machine.idc_id=ci.id and ci.delete_flag=0)
 ...
ORDER BY
id
DESC
LIMIT
0,30

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:

SELECT
*
FROM
CMDB_PHYSICAL_MACHINE machine
JOIN
cmdb_idc ci ON (machine.idc_id=ci.id and ci.delete_flag=0)
WHERE
1=1
AND
machine.delete_flag=0
ORDER BY
machine.id
DESC
LIMIT
0,30;

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:

EXPLAIN SELECT *
FROM
CMDB_PHYSICAL_MACHINE machine
JOIN
cmdb_idc ci ON (machine.idc_id=ci.id)
WHERE
1=1
AND
machine.delete_flag=0
ORDER BY
machine.id
DESC
LIMIT
0,30;

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.