Fixed bug #13273: Caching framework sub-select is slow because mysql does not use...
authorChristian Kuhn <lolli@schwarzbu.ch>
Sun, 28 Mar 2010 14:10:21 +0000 (14:10 +0000)
committerChristian Kuhn <lolli@schwarzbu.ch>
Sun, 28 Mar 2010 14:10:21 +0000 (14:10 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Core/trunk@7204 709f56b5-9817-0410-a4d7-c38de5d9e867

ChangeLog
t3lib/cache/backend/class.t3lib_cache_backend_dbbackend.php

index ac36962..b1374ef 100755 (executable)
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,7 @@
+2010-03-28  Christian Kuhn  <lolli@schwarzbu.ch>
+
+       * Fixed bug #13273: Caching framework sub-select is slow because mysql does not use indexes on outer query
+
 2010-03-27  Benjamin Mack  <benni@typo3.org>
 
        * Fixed bug #12794: t3lib_TStemplate::getFileName() handles path starting with media/ as magic
index b61a8ac..af6af6d 100644 (file)
@@ -299,20 +299,13 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
         * @return void
         */
        public function flushByTag($tag) {
-               $GLOBALS['TYPO3_DB']->exec_DELETEquery(
-                       $this->cacheTable,
-                       'identifier IN (' .
-                               $GLOBALS['TYPO3_DB']->SELECTsubquery(
-                                       'identifier',
-                                       $this->tagsTable,
-                                       $this->getQueryForTag($tag)
-                               ) .
-                       ')'
-               );
+               $tagsTableWhereClause = $this->getQueryForTag($tag);
+
+               $this->deleteCacheTableRowsByTagsTableWhereClause($tagsTableWhereClause);
 
                $GLOBALS['TYPO3_DB']->exec_DELETEquery(
                        $this->tagsTable,
-                       $this->getQueryForTag($tag)
+                       $tagsTableWhereClause
                );
        }
 
@@ -329,20 +322,13 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                                $listQueryConditions[$tag] = $this->getQueryForTag($tag);
                        }
 
-                       $GLOBALS['TYPO3_DB']->exec_DELETEquery(
-                               $this->cacheTable,
-                               'identifier IN (' .
-                                       $GLOBALS['TYPO3_DB']->SELECTsubquery(
-                                               'identifier',
-                                               $this->tagsTable,
-                                               implode(' OR ', $listQueryConditions)
-                                       ) .
-                               ')'
-                       );
+                       $tagsTableWhereClause = implode(' OR ', $listQueryConditions);
+
+                       $this->deleteCacheTableRowsByTagsTableWhereClause($tagsTableWhereClause);
 
                        $GLOBALS['TYPO3_DB']->exec_DELETEquery(
                                $this->tagsTable,
-                               implode(' OR ', $listQueryConditions)
+                               $tagsTableWhereClause
                        );
                }
        }
@@ -354,17 +340,31 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
         * @author Ingo Renner <ingo@typo3.org>
         */
        public function collectGarbage() {
-               $GLOBALS['TYPO3_DB']->exec_DELETEquery(
-                       $this->tagsTable,
-                       'identifier IN (' .
-                               $GLOBALS['TYPO3_DB']->SELECTsubquery(
-                                       'identifier',
-                                       $this->cacheTable,
-                                       'crdate + lifetime < ' . $GLOBALS['EXEC_TIME'] . ' AND lifetime > 0'
-                               ) .
-                       ')'
+                       // Get identifiers of expired cache entries
+               $tagsEntryIdentifierRowsRessource = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
+                       'identifier',
+                       $this->cacheTable,
+                       'crdate + lifetime < ' . $GLOBALS['EXEC_TIME'] . ' AND lifetime > 0'
                );
 
+               $tagsEntryIdentifiers = array();
+               while ($tagsEntryIdentifierRow = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($tagsEntryIdentifierRowsRessource)) {
+                       $tagsEntryIdentifiers[] = $GLOBALS['TYPO3_DB']->fullQuoteStr(
+                               $tagsEntryIdentifierRow['identifier'],
+                               $this->tagsTable
+                       );
+               }
+               $GLOBALS['TYPO3_DB']->sql_free_result($cacheEntryIdentifierRowsRessource);
+
+                       // Delete tag rows connected to expired cache entries
+               if (count($tagsEntryIdentifiers)) {
+                       $GLOBALS['TYPO3_DB']->exec_DELETEquery(
+                               $this->tagsTable,
+                               'identifier IN (' . implode(', ', $tagsEntryIdentifiers) . ')'
+                       );
+               }
+
+                       // Delete expired cache rows
                $GLOBALS['TYPO3_DB']->exec_DELETEquery(
                        $this->cacheTable,
                        'crdate + lifetime < ' . $GLOBALS['EXEC_TIME'] . ' AND lifetime > 0'
@@ -467,6 +467,36 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
 
                return $query;
        }
+
+       /**
+        * Deletes rows in cache table found by where clause on tags table
+        *
+        * @param string The where clause for the tags table
+        * @return void
+        */
+       protected function deleteCacheTableRowsByTagsTableWhereClause($tagsTableWhereClause) {
+               $cacheEntryIdentifierRowsRessource = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
+                       'DISTINCT identifier',
+                       $this->tagsTable,
+                       $tagsTableWhereClause
+               );
+
+               $cacheEntryIdentifiers = array();
+               while ($cacheEntryIdentifierRow = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($cacheEntryIdentifierRowsRessource)) {
+                       $cacheEntryIdentifiers[] = $GLOBALS['TYPO3_DB']->fullQuoteStr(
+                               $cacheEntryIdentifierRow['identifier'],
+                               $this->cacheTable
+                       );
+               }
+               $GLOBALS['TYPO3_DB']->sql_free_result($cacheEntryIdentifierRowsRessource);
+
+               if (count($cacheEntryIdentifiers)) {
+                       $GLOBALS['TYPO3_DB']->exec_DELETEquery(
+                               $this->cacheTable,
+                               'identifier IN (' . implode(', ', $cacheEntryIdentifiers) . ')'
+                       );
+               }
+       }
 }