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

[Xavier Perseguers]

Hi,

> If it's the query
>
> DELETE FROM cachingframework_cache_pages WHERE identifier IN (SELECT
> identifier FROM cachingframework_cache_pages_tags WHERE
> cachingframework_cache_pages_tags.tag = 'pageId_46');
>
> which causes the problem, why not rewrite it to:
>
> DELETE cachingframework_cache_pages FROM cachingframework_cache_pages
> JOIN cachingframework_cache_pages_tags USING (identifier) WHERE
> cachingframework_cache_pages_tags.tag = 'pageId_46';
>
> or
>
> DELETE cachingframework_cache_pages FROM cachingframework_cache_pages
> JOIN cachingframework_cache_pages_tags ON
> cachingframework_cache_pages.identifier =
> cachingframework_cache_pages_tags.identifier WHERE
> cachingframework_cache_pages_tags.tag = 'pageId_46';
>
> Are there problems with DBAL that prevent this syntax from being used?

Yes. There's two problems here:

1) t3lib_sqlparser cannot parse your query. Adding support for this would not be that tricky though
2) we have to make sure the syntax is "somehow" compatible in different DBMS. There was a long discussion on your second proposal and the short answer was: Oracle does not support this.

-- 
Xavier Perseguers
http://xavier.perseguers.ch/en