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

[Jigal van Hemert]

Xavier Perseguers wrote:
>> 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 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?
> 
> 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.

Cross DB support is a PITA ;-)

I performed a quick search and it seems that MySQL and SQL server 
support multi-table deletes (although this variation only deletes from a 
single table) and Oracle does not support multi-table deletes (unless 
they are parent-child tables).
Oracle prefers subqueries in a lot of cases, while MySQL for example 
prefers JOINs (MySQL can not use indexes with subqueries yet).

For a moment it seemed that a multi-table updatable view could be the 
answer, but this does not support DELETE actions on MySQL.

The query structures are so different that it seems hard to write a 
generic function for this (which could be overridden in DBAL for 
DB-specific variations).

Thanks for your investigations!

-- 
Jigal van Hemert.