MySQL SQL optimization: Limitations of correlated subqueries

This is the third article on MySQL SQL optimization.

A company's business system frequently throws a problematic SQL statement. We have made fundamental statistics on such SQL statements:

These SQL statements have been executed for 12 times recently. The maximum time is 480 seconds and the minimum time is 286 seconds.

Table t1 has more than 0.9 million rows, which always scans so much unnecessary data.

This is because the MySQL query optimizer has limitations in processing related subqueries.

MySQL always presses the related outer table into the subquery, which is considered to be more efficient in searching data rows.
This means that MySQL first selects the external layer table for full table scanning, and then executes subqueries one by one based on the relevant fields,

If it is a small table, the situation may not attract our attention, but if the outer layer represents a very large table, the query performance will be very poor,

Unfortunately, our scenario just fits the latter.

Our optimized execution results:

Good Luck!