Use separate tables for tags in the caching framework
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)
#1 Updated by Oliver Hader over 10 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...
#3 Updated by Martin Kutschker over 10 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.
#5 Updated by Martin Kutschker over 10 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 */
DELETE FROM titleauthor
WHERE title_id IN
WHERE title LIKE '%computers%')
/* Transact-SQL extension */
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'
#7 Updated by Xavier Perseguers over 10 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:
#8 Updated by Martin Kutschker over 10 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.
#11 Updated by Martin Kutschker over 10 years ago
After re-reading the docs for Mysql, PostgreSQL and SQL server I find that these three databases support THIS syntax:
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.