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 MariaDB that do not support subqueries. However, in some cases, converting a subquery to a join may improve performance. See , "Rewriting Subqueries as Joins".
• 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 MariaDB facilities like the BENCHMARK() function, you can get an idea about what helps in your own situation. See , "Information Functions".

Some optimizations that MariaDB itself makes are:

• MySQL executes uncorrelated subqueries only once. Use EXPLAIN to make sure that a given subquery really is uncorrelated.
• 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} {> | < | >= | <=} (uncorrelated 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)
  

See also the MariaDB Internals Manual chapter How MariaDB Transforms Subqueries.

Retornar