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

[Xavier Perseguers]

>> 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 ;-)

This is one of the strength of TYPO3 over other CMS ;-)

> 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).

All is already written in bug tracker. I guess (and hope) your investigations were looking there.

> 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).

Just look at what you wrote first:

 >>> 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';

What is preventing us from writing a method that takes "cachingframework_cache_pages", "identifier", "caching_framework_cache_pages_tags" and "tag" as parameters and creates your second query 
(standard for MySQL) while DBAL would override it (at least for Oracle) to be like the first one?

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