Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.6Mb
PDF (A4) - 40.7Mb
PDF (RPM) - 40.3Mb
HTML Download (TGZ) - 10.7Mb
HTML Download (Zip) - 10.7Mb
HTML Download (RPM) - 9.3Mb
Man Pages (TGZ) - 243.0Kb
Man Pages (Zip) - 348.2Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Optimizing Subqueries

13.2.11.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 also Section 8.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;