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

[Christian Kuhn]

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/

Regards
Christian
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 13273_01.diff
URL: <http://lists.typo3.org/pipermail/typo3-team-core/attachments/20100117/d8ea2ccf/attachment.txt>