TYPO3 Core

Be careful with subselects, mysql seems t have some performance problems:
http://bugs.mysql.com/bug.php?id=16159

See also http://bugs.typo3.org/view.php?id=15984 where this affects crawler (here up to 200 sec for a single update).

from: http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html (same also for 5.5) #

Subquery optimization for IN is not as effective as for the = operator or for the IN operator.

A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

The optimizer rewrites the statement to a correlated subquery:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.

An implication is that an IN subquery can be much slower than a query written using an IN operator that lists the same values that the subquery would return. #

Thanks Björn for pointing this out. What would be alternatives in this case? Use separate queries? Or rewrite this part to use LEFT JOIN?

I just added the current version of the patch (without further optimization to the subqueries)...