Mysqlsql optimization: Limitations of associated subqueries

This is the third of MySQL SQL optimizations. A company's business system frequently throws problem SQL, and we do fundamental statistics on this type of sql:

This type of SQL has been executed 12 times in the near future, with a maximum cost of 480 seconds and a minimum of 286 seconds.

The T1 table has more than 900,000 rows and always scans so much unwanted data

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

MySQL always presses the relevant outer table into the subquery, which it considers to be more efficient in finding rows of data.

If it's a small table, the situation may not have caught our attention, but if the outer layer represents a very large table, then the query's performance will be very bad,

Unfortunately, our scene is just the latter.

Our optimized execution effect:

Good luck!