Testing versioned#213

legacyQuery.sql

SELECT DISTINCT `SiteTree_Versions`.`ClassName`, `SiteTree_Versions`.`LastEdited`, `SiteTree_Versions`.`Created`, `SiteTree_Versions`.`CanViewType`, `SiteTree_Versions`.`CanEditType`, `SiteTree_Versions`.`Version`, `SiteTree_Versions`.`URLSegment`, `SiteTree_Versions`.`Title`, `SiteTree_Versions`.`MenuTitle`, `SiteTree_Versions`.`Content`, `SiteTree_Versions`.`MetaDescription`, `SiteTree_Versions`.`ExtraMeta`, `SiteTree_Versions`.`ShowInMenus`, `SiteTree_Versions`.`ShowInSearch`, `SiteTree_Versions`.`Sort`, `SiteTree_Versions`.`HasBrokenFile`, `SiteTree_Versions`.`HasBrokenLink`, `SiteTree_Versions`.`ReportClass`, `SiteTree_Versions`.`RelationFieldsTestPageID`, `SiteTree_Versions`.`SubsiteID`, `SiteTree_Versions`.`ParentID`, `SiteTree_Versions`.`RecordID` AS `ID`,
			CASE WHEN `SiteTree_Versions`.`ClassName` IS NOT NULL THEN `SiteTree_Versions`.`ClassName`
			ELSE 'SilverStripe\\CMS\\Model\\SiteTree' END AS `RecordClassName`, `SiteTree_Versions`.`RecordID`, `SiteTree_Versions`.`WasPublished`, `SiteTree_Versions`.`WasDeleted`, `SiteTree_Versions`.`WasDraft`, `SiteTree_Versions`.`AuthorID`, `SiteTree_Versions`.`PublisherID`
 FROM `SiteTree_Versions` INNER JOIN             (
            SELECT `SiteTree_Versions`.`RecordID`,
                MAX(`SiteTree_Versions`.`Version`) AS `LatestVersion`
            FROM `SiteTree_Versions`
            WHERE `SiteTree_Versions`.`LastEdited` <= '2020-01-01'
                AND `SiteTree_Versions`.`WasDraft` = 1
            GROUP BY `SiteTree_Versions`.`RecordID`
            ) AS `SiteTree_Versions_Latest` ON             `SiteTree_Versions_Latest`.`RecordID` = `SiteTree_Versions`.`RecordID`
            AND `SiteTree_Versions_Latest`.`LatestVersion` = `SiteTree_Versions`.`Version`
 WHERE (`SiteTree_Versions`.`WasDeleted` = 0)
 AND (`SiteTree_Versions`.`SubsiteID` IN (0))
 ORDER BY `SiteTree_Versions`.`Sort` ASC

prQuery.sql

SELECT DISTINCT `SiteTree_Versions`.`ClassName`, `SiteTree_Versions`.`LastEdited`, `SiteTree_Versions`.`Created`, `SiteTree_Versions`.`CanViewType`, `SiteTree_Versions`.`CanEditType`, `SiteTree_Versions`.`Version`, `SiteTree_Versions`.`URLSegment`, `SiteTree_Versions`.`Title`, `SiteTree_Versions`.`MenuTitle`, `SiteTree_Versions`.`Content`, `SiteTree_Versions`.`MetaDescription`, `SiteTree_Versions`.`ExtraMeta`, `SiteTree_Versions`.`ShowInMenus`, `SiteTree_Versions`.`ShowInSearch`, `SiteTree_Versions`.`Sort`, `SiteTree_Versions`.`HasBrokenFile`, `SiteTree_Versions`.`HasBrokenLink`, `SiteTree_Versions`.`ReportClass`, `SiteTree_Versions`.`RelationFieldsTestPageID`, `SiteTree_Versions`.`SubsiteID`, `SiteTree_Versions`.`ParentID`, `SiteTree_Versions`.`RecordID` AS `ID`,
			CASE WHEN `SiteTree_Versions`.`ClassName` IS NOT NULL THEN `SiteTree_Versions`.`ClassName`
			ELSE 'SilverStripe\\CMS\\Model\\SiteTree' END AS `RecordClassName`, `SiteTree_Versions`.`RecordID`, `SiteTree_Versions`.`WasPublished`, `SiteTree_Versions`.`WasDeleted`, `SiteTree_Versions`.`WasDraft`, `SiteTree_Versions`.`AuthorID`, `SiteTree_Versions`.`PublisherID`
 FROM `SiteTree_Versions`
 WHERE (`SiteTree_Versions`.`WasDeleted` = 0)
 AND (`SiteTree_Versions`.`Version` =         (
            SELECT MAX(`SiteTree_Versions_Latest`.`Version`)
            FROM `SiteTree_Versions` AS `SiteTree_Versions_Latest`
            WHERE `SiteTree_Versions_Latest`.`LastEdited` <= '2020-01-01'
                AND `SiteTree_Versions_Latest`.`RecordID` = `SiteTree_Versions`.`RecordID`
                AND `SiteTree_Versions_Latest`.`WasDraft` = 1
        ))
 AND (`SiteTree_Versions`.`Version` IS NOT NULL)
 AND (`SiteTree_Versions`.`SubsiteID` IN (0))
 ORDER BY `SiteTree_Versions`.`Sort` ASC

queryRunner.bash

for run in {1..100}
do
  mysql SS_44 < $1 > /dev/null
done

singleLegacyQuery.sql

SELECT DISTINCT `SiteTree_Versions`.`ClassName`, `SiteTree_Versions`.`LastEdited`, `SiteTree_Versions`.`Created`, `SiteTree_Versions`.`CanViewType`, `SiteTree_Versions`.`CanEditType`, `SiteTree_Versions`.`Version`, `SiteTree_Versions`.`URLSegment`, `SiteTree_Versions`.`Title`, `SiteTree_Versions`.`MenuTitle`, `SiteTree_Versions`.`Content`, `SiteTree_Versions`.`MetaDescription`, `SiteTree_Versions`.`ExtraMeta`, `SiteTree_Versions`.`ShowInMenus`, `SiteTree_Versions`.`ShowInSearch`, `SiteTree_Versions`.`Sort`, `SiteTree_Versions`.`HasBrokenFile`, `SiteTree_Versions`.`HasBrokenLink`, `SiteTree_Versions`.`ReportClass`, `SiteTree_Versions`.`RelationFieldsTestPageID`, `SiteTree_Versions`.`SubsiteID`, `SiteTree_Versions`.`ParentID`, `SiteTree_Versions`.`RecordID` AS `ID`,
			CASE WHEN `SiteTree_Versions`.`ClassName` IS NOT NULL THEN `SiteTree_Versions`.`ClassName`
			ELSE 'SilverStripe\\CMS\\Model\\SiteTree' END AS `RecordClassName`, `SiteTree_Versions`.`RecordID`, `SiteTree_Versions`.`WasPublished`, `SiteTree_Versions`.`WasDeleted`, `SiteTree_Versions`.`WasDraft`, `SiteTree_Versions`.`AuthorID`, `SiteTree_Versions`.`PublisherID`
 FROM `SiteTree_Versions` INNER JOIN             (
            SELECT `SiteTree_Versions`.`RecordID`,
                MAX(`SiteTree_Versions`.`Version`) AS `LatestVersion`
            FROM `SiteTree_Versions`
            WHERE `SiteTree_Versions`.`LastEdited` <= '2020-01-01'
                AND `SiteTree_Versions`.`WasDraft` = 1
            GROUP BY `SiteTree_Versions`.`RecordID`
            ) AS `SiteTree_Versions_Latest` ON             `SiteTree_Versions_Latest`.`RecordID` = `SiteTree_Versions`.`RecordID`
            AND `SiteTree_Versions_Latest`.`LatestVersion` = `SiteTree_Versions`.`Version`
 WHERE (`SiteTree_Versions`.`RecordID` = 100)
 AND (`SiteTree_Versions`.`WasDeleted` = 0)
 ORDER BY `SiteTree_Versions`.`Sort` ASC
 LIMIT 1

singlePrQuery.sql

SELECT DISTINCT `SiteTree_Versions`.`ClassName`, `SiteTree_Versions`.`LastEdited`, `SiteTree_Versions`.`Created`, `SiteTree_Versions`.`CanViewType`, `SiteTree_Versions`.`CanEditType`, `SiteTree_Versions`.`Version`, `SiteTree_Versions`.`URLSegment`, `SiteTree_Versions`.`Title`, `SiteTree_Versions`.`MenuTitle`, `SiteTree_Versions`.`Content`, `SiteTree_Versions`.`MetaDescription`, `SiteTree_Versions`.`ExtraMeta`, `SiteTree_Versions`.`ShowInMenus`, `SiteTree_Versions`.`ShowInSearch`, `SiteTree_Versions`.`Sort`, `SiteTree_Versions`.`HasBrokenFile`, `SiteTree_Versions`.`HasBrokenLink`, `SiteTree_Versions`.`ReportClass`, `SiteTree_Versions`.`RelationFieldsTestPageID`, `SiteTree_Versions`.`SubsiteID`, `SiteTree_Versions`.`ParentID`, `SiteTree_Versions`.`RecordID` AS `ID`, 
			CASE WHEN `SiteTree_Versions`.`ClassName` IS NOT NULL THEN `SiteTree_Versions`.`ClassName`
			ELSE 'SilverStripe\\CMS\\Model\\SiteTree' END AS `RecordClassName`, `SiteTree_Versions`.`RecordID`, `SiteTree_Versions`.`WasPublished`, `SiteTree_Versions`.`WasDeleted`, `SiteTree_Versions`.`WasDraft`, `SiteTree_Versions`.`AuthorID`, `SiteTree_Versions`.`PublisherID`
 FROM `SiteTree_Versions`
 WHERE (`SiteTree_Versions`.`RecordID` = 100)
 AND (`SiteTree_Versions`.`WasDeleted` = 0)
 AND (`SiteTree_Versions`.`Version` =         (
            SELECT MAX(`SiteTree_Versions_Latest`.`Version`)
            FROM `SiteTree_Versions` AS `SiteTree_Versions_Latest`
            WHERE `SiteTree_Versions_Latest`.`LastEdited` <= '2020-01-01'
                AND `SiteTree_Versions_Latest`.`RecordID` = `SiteTree_Versions`.`RecordID`
                AND `SiteTree_Versions_Latest`.`WasDraft` = 1
        ))
 AND (`SiteTree_Versions`.`Version` IS NOT NULL)
 ORDER BY `SiteTree_Versions`.`Sort` ASC
 LIMIT 1