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

[Susanne Moog]

Hi,

Am 17.01.2010 19:52, schrieb 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.

in general I was able to reproduce the issue with using about 2200 rows
and the mentioned EXPLAIN statements. In my tests the two single queries
where also both using indexes on mysql 5.0.83 - with one query only the
inner one does. So generally +1.

I noticed one thing by reading:

$tagsTableWhereClause = $this->getQueryForTag($tag);

(...)
$GLOBALS['TYPO3_DB']->exec_DELETEquery(
	$this->tagsTable,
	$this->getQueryForTag($tagsTableWhereClause)
);


The last param of the query should probably be only
$tagsTableWhereClause, without the additional "getQueryForTag".

Best regards,

Susanne