MySQL :: MySQL 8.4 Reference Manual :: 15.2.15.11 Optimizing Subqueries (original) (raw)

The world's most popular open source database

15.2.15.11 Optimizing Subqueries

Development is ongoing, so no optimization tip is reliable for the long term. The following list provides some interesting tricks that you might want to play with. See alsoSection 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”.

• Move clauses from outside to inside the subquery. For example, use this query:

SELECT * FROM t1  
  WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);  

Instead of this query:

SELECT * FROM t1  
  WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);  

For another example, use this query:

SELECT (SELECT column1 + 5 FROM t1) FROM t2;  

Instead of this query:

SELECT (SELECT column1 FROM t1) + 5 FROM t2;