[TYPO3-core] RFC #13273: Bug: Caching framework sub-select is slow because mysql does not use indexes on outer query

[Christian Kuhn]

Reminder #1

This issue is a *real* show stopper of db backend in production.

Cheers
Christian

Christian Kuhn wrote:
> This is an SVN patch request.
> 
> Type: bugfix
> 
> BT reference: http://bugs.typo3.org/view.php?id=13273
> 
> Branches: trunk, TYPO3_4-3
> 
> 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/