How to Cut a Production MySQL 8.0 LATERAL Query from 3 s to 0.8 s

Problem Identification

Monitoring shows the target query runs over 700 times per hour with an average execution time of 2 seconds. The original SQL joins cont_execute exe with a derived subquery that selects the latest review record per contract.

select overdue_amount
from cont_execute exe
left join (
  SELECT cont_number,
         is_important_cont,
         is_important_cont_in,
         ROW_NUMBER() OVER (PARTITION BY cont_number ORDER BY create_time DESC) AS rn
  FROM cont_review_main
  WHERE del_flag = 0
) main on exe.cont_number = main.cont_number
where exe.del_flag = 0
  and main.rn = 1
  and main.is_important_cont_in = 0
  and exe.cont_company_name = 'xx科技有限公司';

The monitoring tool fails to suggest useful optimizations; it only recommends removing redundant indexes, which does not improve performance.

Execution‑Plan Analysis

The plan reveals three key issues:

Derived table <derived2> has no index , causing a full scan of 77,724 rows and a filesort.

The cont_review_main scan uses the index idx_htps1_main on del_flag, but the PARTITION BY cont_number ORDER BY create_time DESC still requires an additional sort.

The exe table is accessed via the primary key cont_number, which is efficient.

Optimization Strategy

MySQL 8.0.14+ introduces the LATERAL keyword, allowing a subquery to reference columns of the outer row. Rewriting the query with a LATERAL join eliminates the need to materialize the large derived table.

SELECT exe.overdue_amount
FROM cont_execute exe
INNER JOIN LATERAL (
  SELECT is_important_cont,
         is_important_cont_in
  FROM cont_review_main main
  WHERE main.cont_number = exe.cont_number
    AND main.del_flag = 0
  ORDER BY main.create_time DESC
  LIMIT 1
) main ON main.is_important_cont_in = 0
WHERE exe.del_flag = 0
  AND exe.cont_company_name = '伟仕佳杰（重庆）科技有限公司';

Advantages: LATERAL lets the subquery filter rows using exe.cont_number one‑by‑one, avoiding the full scan of the derived table.

Required index:

CREATE INDEX idx_main_cont_time
ON cont_review_main(cont_number, create_time DESC, del_flag);

After applying the index and the LATERAL rewrite, the query runs in 0.75 seconds on a dataset of 30 k rows, a clear performance gain.

Understanding LATERAL

LATERAL means “horizontal association” or “row‑by‑row reference”. Unlike a regular subquery, which is executed once independently, a LATERAL subquery is evaluated for each outer row, allowing it to use the outer row’s values.

Ordinary subqueries are executed independently; LATERAL subqueries can reference the current outer row.

Example of a failing ordinary subquery:

-- Error: exe.cont_number is unknown inside the subquery
SELECT *
FROM cont_execute exe
INNER JOIN (
  SELECT *
  FROM cont_review_main m
  WHERE m.cont_number = exe.cont_number
  LIMIT 1
) main;

Correct LATERAL version:

-- Works: LATERAL allows reference to exe.cont_number
SELECT *
FROM cont_execute exe
INNER JOIN LATERAL (
  SELECT *
  FROM cont_review_main m
  WHERE m.cont_number = exe.cont_number
  ORDER BY m.create_time DESC
  LIMIT 1
) main;

Analogy:

Ordinary subquery: compute the whole address book first, then distribute it.

LATERAL: for each person, look up the phone number on the spot.

Logical Comparison Before and After

The two SQL forms are logically equivalent if create_time is unique. Both select the latest review record where is_important_cont_in = 0 and a matching audit record exists.

If multiple rows share the same create_time, the result becomes nondeterministic: the original window‑function version may pick any of the tied rows, while the LATERAL version also depends on the order of index scans. Therefore, it is recommended to add a tie‑breaker such as the primary key id:

ORDER BY create_time DESC, id DESC

Conclusions and Recommendations

Assuming create_time is unique, both the LATERAL‑based query and the window‑function query produce identical results.

If duplicate timestamps exist, both queries may return different rows; this nondeterministic behavior should be avoided.

To guarantee deterministic results, extend the ordering clause with a unique column (e.g., id DESC).