Bug #21017

Use separate tables for tags in the caching framework

Added by Oliver Hader almost 12 years ago. Updated almost 11 years ago.

Should have
Target version:
Start date:
Due date:
% Done:


Estimated time:
TYPO3 Version:
PHP Version:
Is Regression:
Sprint Focus:


The database backend of the caching framework stores tags in a comma separated list. Searching for tags results in a LIKE query which will be very slow when there are many cache entries.

(issue imported from #M11903)


0011903_v0.patch (9.63 KB) 0011903_v0.patch Administrator Admin, 2009-09-08 18:43
0011903_v1.patch (12.2 KB) 0011903_v1.patch Administrator Admin, 2009-09-08 20:26
0011903_v2.patch (12.2 KB) 0011903_v2.patch Administrator Admin, 2009-09-10 15:24
0011903_v4.patch (14.8 KB) 0011903_v4.patch Administrator Admin, 2009-09-18 16:55
0011903_v6.patch (17.3 KB) 0011903_v6.patch Administrator Admin, 2009-09-21 15:12

Related issues

Related to TYPO3 Core - Bug #20863: TCEmain clears cache inefficientlyClosedIngo Renner2009-08-11

Related to TYPO3 Core - Bug #21718: Add functionality to work with caching frameworkClosedOliver Hader2009-11-27

Related to TYPO3 Core - Feature #21688: Add support for IN and EXISTS subqueriesClosedXavier Perseguers2009-11-25


Updated by Oliver Hader almost 12 years ago

The attached patch uses a separate table to store tags. Thus, it's faster for selecting data.
However, due to the lack of JOINs in TYPO3_DB, the flush operations and garbage collection are very inefficient since they have to use iterations over the cache identifiers and will create a lot of DELETE queries...


Updated by Oliver Hader almost 12 years ago

Extended TYPO3_DB to support something like

DELETE tbl1 FROM tbl1, tbl2 WHERE tbl1.identifier = tbl2.identifier;

(this has to be in a separate RFC when posting to the Core List).


Updated by Martin Kutschker over 11 years ago

A DELETE with multiple tables is non-standard.

PostgreSQL has the non-standard USING clause.

DELETE FROM tbl1 USING tbl2 WHERE tbl1.identifier = tbl2.identifier;


A more portable way would be to use sub-selects. Maybe the DBAL team is possible to come up with a portable solution.


Updated by Martin Kutschker over 11 years ago

A note in a forum claimed that this will work with Oracle 9i:

DELETE FROM (tbl1, tbl2 WHERE tbl1.identifier = tbl2.identifier);

Though a reply mentioned an error when executing this command.


Updated by Martin Kutschker over 11 years ago

SQL Server 2000 seems also to support the extended syntax like Mysql.


It offers also an example how to make the DELETE without this extension. The solution requires sub-selects.

/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')

/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'


Updated by Martin Kutschker over 11 years ago

Extending the DELETEquery function like this should do the trick:


This will maybe not allow for all possible SQL query variations, but will be sufficient for most cases.


Updated by Xavier Perseguers over 11 years ago


Trying your stuff on Oracle 11 leads to this:

DELETE FROM (T1, T2 WHERE T1.column1 = T2.column2);

Column 13: Missing SELECT keyword

In addition, it seems that this cannot be achieved without using triggers or ON CASCADE DELETE:


Updated by Martin Kutschker over 11 years ago

Xavier, this is the error I mentioned. The thread you link to suggests this syntax:

DELETE FROM (SELECT * FROM tbl1, tbl2 WHERE tbl1.identifier = tbl2.identifier);

Anyway, as Oracle supports sub-queries, DBAL can provide a variant suitable for it. And as a fallback it still could use a SELECT and multiple DELETEs.


Updated by Xavier Perseguers over 11 years ago

OK. With SELECT, at least one of the table should have a key, which I don't have ATM otherwise I get the same error as in the thread I linked. But as you said, we certainly can find a way for DBAL to work with this.


Updated by Oliver Hader over 11 years ago

Committed to SVN Trunk (rev. 6025)


Updated by Martin Kutschker over 11 years ago

After re-reading the docs for Mysql, PostgreSQL and SQL server I find that these three databases support THIS syntax:

[FROM] tbl_name
USING|FROM table_references
WHERE where_condition

Note: Mysql and Posgresql use USING, whereas SQL server uses a second FROM. The "table_references" can be anything that is found in the FROM clause of a SELECT statement.

Unfortunately this syntax deletes only from one table. Neither PostgreSQL nor SQL server allow multi-tables in the (first) FROM. The USING (second FROM) is only used for a join.

Still looking for a DBAL way to get a portable simple solution for the cache clearing problem.

Also available in: Atom PDF