TYPO3 Core

Problem:
mysql is unable to use indexes for outer query if where clause is a sub select [1]. This makes tag-deletion in the caching framework with db backend very slow on bigger tables because mysql happily decides to perform a full table scan.

Solution:
Separate queries and do more logic with php.

Notes:
Running a production site with enabled caching framework frequently showed us slow queries (>1s) like those:
DELETE FROM cachingframework_cache_pages WHERE identifier IN (SELECT identifier FROM cachingframework_cache_pages_tags WHERE cachingframework_cache_pages_tags.tag = 'pageId_46');
Changing "DELETE FROM" to "EXPLAIN SELECT * FROM" showed us mysql does not use the defined index for the outer identifier field (handled rows = number of rows in table, possible keys NULL, no key used). After splitting the sub select into an own query no more slow queries occured in this part of the caching framework. Two single queries had a performance factor of 1000 (from 2-5 seconds to some milliseconds) on a cachingframework_cache_pages table with ~100k entries and ~2gig size.

The host is running debian stable with mysql 5.0.51, I would be interested if newer mysql version still suffer from this issue.

Warning: Test this with real data and big tables only. If there are not enough rows in a table, mysql might decide to ignore indexes completely anyway, even if they exist and could be used.

[1] http://www.mysqlperformanceblog.com/2006/08/31/derived-tables-and-views-performance/

(issue imported from #M13273)