Bug #86945

Updated by Alexander Opitz over 2 years ago

All nodes of a PageTree will call TYPO3\CMS\Workspaces\Service\WorkspaceService::hasPageRecordVersions($workspaceId, $pageId) which themself calls TYPO3\CMS\Workspaces\Service\WorkspaceService::fetchPagesWithVersionsInTable($workspaceId, $tableName); for every versionised table. This function leads to a SQL STatement, whcih will get very slow on large tables. 

 SQL Statement: 

 <pre><code class="sql"> 
 SELECT `B`.`pid` AS `pageId` FROM `sys_file_reference` `B` WHERE ( 
 (`B`.`uid` IN ( 
     SELECT `A`.`t3ver_oid` FROM `sys_file_reference` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 14) AND (`A`.`t3ver_state` <> 4) AND (`A`.`deleted` = 0))) 
 OR (`B`.`t3ver_move_id` IN ( 
     SELECT `A`.`t3ver_oid` FROM `sys_file_reference` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 14) AND (`A`.`t3ver_state` = 4) AND (`A`.`deleted` = 0)))) 
 AND (`B`.`deleted` = 0) GROUP BY `B`.`pid` 
 </code></pre> 

 The problem is the OR between the two IN with SQL Subselect, cause this leads in MySQL/MariaDB to processing the OUTER SQL before the INNER SQL. (IMHO in MariaDB this issue is tried to address) 

 TIME: 10.424 s 

 EXPLAIN: EXPLAIN 
 <pre> 
 id 	 select_type 	    table 	 type 	 possible_keys 	 key 	 key_len 	 ref 	        rows 	 Extra 
 1 	 PRIMARY 	            B 	 index 	 deleted 		  parent 	 5 	 NULL 	        11273 	 Using where 
 3 	 DEPENDENT SUBQUERY 	 A 	 ref 	 parent,deleted 	 parent 	 5 	 const,const 	 653 	 Using where 
 2 	 DEPENDENT SUBQUERY 	 A 	 ref 	 parent,deleted 	 parent 	 5 	 const,const 	 653 	 Using where 
 </pre> 

 Which needs around 10 seconds on demo system. 

 Better use JOIN Statement: 
 <pre><code class="sql"> 
 SELECT `B`.`pid` AS `pageId` FROM `sys_file_reference` `B`  
 JOIN (SELECT `t3ver_oid` FROM `sys_file_reference` WHERE (`pid` = -1) AND (`t3ver_wsid` = 14) AND (`t3ver_state` <> 4) AND (`deleted` = 0)) `A`  
   ON `B`.`uid` = `A`.`t3ver_oid` 
 WHERE  
 (`B`.`deleted` = 0) GROUP BY `B`.`pid` 
 UNION SELECT `B`.`pid` AS `pageId` FROM `sys_file_reference` `B`  
 JOIN (SELECT `t3ver_oid` FROM `sys_file_reference` WHERE (`pid` = -1) AND (`t3ver_wsid` = 14) AND (`t3ver_state` = 4) AND (`deleted` = 0)) `C`  
   ON `B`.`t3ver_move_id` = `C`.`t3ver_oid` 
 WHERE  
 (`B`.`deleted` = 0) GROUP BY `B`.`pid` 
 </code></pre> 

 TIME: 0.002 s 
 EXPLAIN 

 <pre> 
 id 	 select_type 	 table 	 type 	 possible_keys 	 key 	 key_len 	 ref 	 rows 	 Extra 
 1 	 PRIMARY 	 <derived2> 	 ALL 	 NULL 	 NULL 	 NULL 	 NULL 	 26 	 Using temporary; Using filesort 
 1 	 PRIMARY 	 B 	 eq_ref 	 PRIMARY,deleted 	 PRIMARY 	 4 	 A.t3ver_oid 	 1 	 Using where 
 2 	 DERIVED 	 sys_file_reference 	 ref 	 parent,deleted 	 parent 	 5 	 	 653 	 Using where 
 3 	 UNION 	 NULL 	 NULL 	 NULL 	 NULL 	 NULL 	 NULL 	 NULL 	 Impossible WHERE noticed after reading const tables 
 4 	 DERIVED 	 sys_file_reference 	 ref 	 parent,deleted 	 parent 	 5 	 	 653 	 Using where 
 NULL 	 UNION RESULT 	 <union1,3> 	 ALL 	 NULL 	 NULL 	 NULL 	 NULL 	 NULL 	 
 </pre> 
 Using UNION to merge both results (like OR) together. But Union is not possible with QueryBuilder but IMHO it would be sufficient in speed to get the data with 2 SQL statements. 
 This issue happened on a larger custom system after migrating from 7LTS to 8LTS, will check what changed and what may help.

Back