MySQLSQL optimization: Limitations of correlated subqueries _ MySQL

MySQLSQL optimization: the limitations of correlated subqueries are the third article of 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.

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!