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) 

 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> 

 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