[TASK] Speed up updating the extension list 76/27876/4
authorBenjamin Mack <benni@typo3.org>
Wed, 26 Feb 2014 17:51:46 +0000 (18:51 +0100)
committerHelmut Hummel <helmut.hummel@typo3.org>
Thu, 27 Feb 2014 16:20:22 +0000 (17:20 +0100)
The process of updating the TER
extension list takes approx 1 minute
because the extension manager needs
to mark all "latest versions". This is done
via a large UPDATE query on fields
without indices, additionally this is done
in PHP and not in SQL with a
simple subselect.

Additionally the SQL file does not set
appropriate indices at all, which is also
done in this patch.

Releases: 6.2
Resolves: #56354
Change-Id: Ic46994fa1b16cce9912950520955185f3f95fe1a
Reviewed-on: https://review.typo3.org/27876
Reviewed-by: Steffen Ritter
Tested-by: Steffen Ritter
Reviewed-by: Helmut Hummel
Tested-by: Helmut Hummel
typo3/sysext/extensionmanager/Classes/Domain/Repository/ExtensionRepository.php
typo3/sysext/extensionmanager/ext_tables.sql

index 554ca72..ad4a506 100644 (file)
@@ -262,17 +262,11 @@ class ExtensionRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {
         * @return void
         */
        protected function markExtensionWithMaximumVersionAsCurrent($repositoryUid) {
-               $whereClauseParts = array();
-               foreach ($this->fetchMaximalVersionsForAllExtensions($repositoryUid) as $row) {
-                       $whereClauseParts[] = '(extension_key = ' . $this->databaseConnection->fullQuoteStr($row['extension_key'], self::TABLE_NAME) .
-                               ' AND integer_version = ' . (int)$row['max_version'] . ')';
-               }
-
-               $whereClause = implode(' OR ', $whereClauseParts);
+               $uidsOfCurrentVersion = $this->fetchMaximalVersionsForAllExtensions($repositoryUid);
 
                $this->databaseConnection->exec_UPDATEquery(
                        self::TABLE_NAME,
-                       $whereClause,
+                       'uid IN (' . implode(',', $uidsOfCurrentVersion) . ')',
                        array(
                                'current_version' => 1,
                        )
@@ -280,18 +274,27 @@ class ExtensionRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {
        }
 
        /**
-        * Fetches the maximal version for all extensions.
+        * Fetches the UIDs of all maximal versions for all extensions.
+        * This is done by doing a subselect in the WHERE clause to get all
+        * max versions and then the UID of that record in the outer select.
         *
         * @param int $repositoryUid
         * @return array
         */
        protected function fetchMaximalVersionsForAllExtensions($repositoryUid) {
-               return $this->databaseConnection->exec_SELECTgetRows(
-                       'extension_key, max(integer_version) AS max_version',
-                       self::TABLE_NAME,
-                       'repository = ' . (int)$repositoryUid,
-                       'extension_key'
+               $extensionUids = $this->databaseConnection->exec_SELECTgetRows(
+                       'a.uid AS uid',
+                       self::TABLE_NAME . ' a',
+                       'integer_version=(' .
+                               $this->databaseConnection->SELECTquery(
+                                       'MAX(integer_version)',
+                                       self::TABLE_NAME . ' b',
+                                       'b.repository=' . (int)$repositoryUid . ' AND a.extension_key=b.extension_key'
+                               ) .
+                       ') AND repository=' . (int)$repositoryUid,
+                       '', '', '', 'uid'
                );
+               return array_keys($extensionUids);
        }
 
        /**
index a2ef594..18b73df 100644 (file)
@@ -41,5 +41,8 @@ CREATE TABLE tx_extensionmanager_domain_model_extension (
   current_version int(3) NOT NULL default '0',
   lastreviewedversion int(3) NOT NULL default '0',
   PRIMARY KEY (uid),
+  KEY index_extrepo (repository,extension_key),
+  KEY index_versionrepo (repository,integer_version),
+  KEY index_currentversions (review_state,current_version),
   UNIQUE versionextrepo (extension_key,version,repository)
 );