Optimizing Subqueries

Development is ongoing, so no optimization tip is reliable for the long term. Some interesting tricks that you might want to play with are:

Using subquery clauses which 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);

Replacing a join with a subquery, for example

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

instead of

SELECT DISTINCT t1.column1 FROM t1, t2
 WHERE t1.column1 = t2.column1;

Moving clauses from outside to inside the subquery, for example:

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

instead of

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

Para outro exemplo:

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

em vez de:

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

Using a row subquery instead of a correlated subquery, for example:

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

instead of

SELECT * FROM t1
 WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
 AND t2.column2=t1.column2);

Using NOT (a = ANY (...)) rather than a <> ALL (...).
Using x = ANY (table containing {1,2}) rather than x=1 OR x=2.
Using = ANY rather than EXISTS

The above tricks may 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. Don't worry too much about transforming to joins except for compatibility with older versions.

Some optimizations that MariaDB itself will make are:

MySQL will execute non-correlated subqueries only once, (use EXPLAIN to make sure that a given subquery really is non-correlated),
MySQL will rewrite IN/ALL/ANY/SOME subqueries in an attempt to take advantage of the possibility that the select-list columns in the subquery are indexed,
MySQL will replace subqueries of the form
```
... IN (SELECT indexed_column FROM single_table ...)
```
with an index-lookup function, which EXPLAIN will describe as a special join type,
MySQL will enhance expressions of the form
```
value {ALL|ANY|SOME} {> | < | >= | <=} (non-correlated subquery)
```
with an expression involving MIN or MAX (unless NULLs or empty sets are involved). For example,
```
WHERE 5 > ALL (SELECT x FROM t)
```
might be treated as
```
WHERE 5 > (SELECT MAX(x) FROM t)
```

There is a chapter titled How MariaDB Transforms Subqueries in the MariaDB Internals Manual, which you can find by downloading the MariaDB source package and looking for a file named internals.texi.

Optimizing Subqueries

Retornar