Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

13.2.8.10. 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:

  • Use subquery clauses that affect the number or order of the rows in the subquery. For example:

    SELECT * FROM t1 WHERE t1.column1 IN
    (SELECT column1 FROM t2 ORDER BY column1);
    SELECT * FROM t1 WHERE t1.column1 IN
    (SELECT DISTINCT column1 FROM t2);
    SELECT * FROM t1 WHERE EXISTS
    (SELECT * FROM t2 LIMIT 1);
    
  • Replace a join with a subquery. For example, try this:

    SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
    SELECT column1 FROM t2);
    

    Instead of this:

    SELECT DISTINCT t1.column1 FROM t1, t2
    WHERE t1.column1 = t2.column1;
    
  • Some subqueries can be transformed to joins for compatibility with older versions of MySQL that do not support subqueries. However, in some cases, converting a subquery to a join may improve performance. See Section 13.2.8.11, “Rewriting Subqueries as Joins for Earlier MySQL Versions”.

  • 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;
    
  • Use a row subquery instead of a correlated subquery. For example, use this query:

    SELECT * FROM t1
    WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);
    

    Instead of this query:

    SELECT * FROM t1
    WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
    AND t2.column2=t1.column2);
    
  • Use NOT (a = ANY (...)) rather than a <> ALL (...).

  • Use x = ANY (table containing (1,2)) rather than x=1 OR x=2.

  • Use = ANY rather than EXISTS.

  • For uncorrelated subqueries that always return one row, IN is always slower than =. For example, use this query:

    SELECT * FROM t1 WHERE t1.col_name
    = (SELECT a FROM t2 WHERE b = some_const);
    

    Instead of this query:

    SELECT * FROM t1 WHERE t1.col_name
    IN (SELECT a FROM t2 WHERE b = some_const);
    

These tricks might cause programs to go faster or slower. Using MySQL facilities like the BENCHMARK() function, you can get an idea about what helps in your own situation. See Section 12.10.3, “Information Functions”.

Some optimizations that MySQL itself makes are:

  • MySQL executes non-correlated subqueries only once. Use EXPLAIN to make sure that a given subquery really is non-correlated.

  • MySQL rewrites IN, ALL, ANY, and SOME subqueries in an attempt to take advantage of the possibility that the select-list columns in the subquery are indexed.

  • MySQL replaces subqueries of the following form with an index-lookup function, which EXPLAIN describes as a special join type (unique_subquery or index_subquery):

    ... IN (SELECT indexed_column FROM single_table ...)
    
  • MySQL enhances expressions of the following form with an expression involving MIN() or MAX(), unless NULL values or empty sets are involved:

    value {ALL|ANY|SOME} {> | < | >= | <=} (non-correlated subquery)
    

    For example, this WHERE clause:

    WHERE 5 > ALL (SELECT x FROM t)
    

    might be treated by the optimizer like this:

    WHERE 5 > (SELECT MAX(x) FROM t)
    

There is a chapter titled “How MySQL Transforms Subqueries” in the MySQL Internals Manual, available at https://dev.mysql.com/doc/.


 
 
  Published under the terms of the GNU General Public License Design by Interspire