Skip to content

perf: tweak some queries in mkpkglists

Mario Oenning requested to merge moson/aurweb:optimize-queries-mkpkglists into master

We can omit the "distinct" from some queries because constraints in the DB ensure uniqueness:

Groups sub-query
  • PackageGroups: Primary key makes PackageID + GroupID unique
  • Groups: Unique index on Name column
    -> Technically we can't have a package with the same group-name twice
Licenses sub-query:
  • PackageLicenses -> Primary key makes PackageID + LicenseID unique
  • Licenses -> Unique index on Name column
    -> Technically we can't have a package with the same license-name twice
Keywords sub-query:
  • PackageKeywords -> Primary key makes PackageBaseID + KeywordID unique
    (And a Package can only have one PackageBase)
  • Keywords -> Unique index on Name column
    -> Technically we can't have a package with the same Keyword twice
Packages main-query:
  • We join PackageBases and Users on their primary key columns
    (which are guaranteed to be unique)
    -> There is no way we could end up with more than one record for a Package...

The following (ORM generated) queries should return the same results, before and after.
(As a check, one could wrap it in a select count(*) from (--query--) q, assuming data does not change in-between 😉)

SQL: Sub-queries (before) 
SELECT anon_1.`ID` AS `anon_1_ID`,
       anon_1.`Type` AS `anon_1_Type`,
       anon_1.`Name` AS `anon_1_Name`,
       anon_1.`Cond` AS `anon_1_Cond`
FROM (
        (SELECT DISTINCT `PackageDepends`.`PackageID` AS `ID`,
                         `DependencyTypes`.`Name` AS `Type`,
                         `PackageDepends`.`DepName` AS `Name`,
                         `PackageDepends`.`DepCondition` AS `Cond`
         FROM `PackageDepends`
         INNER JOIN `DependencyTypes` ON `DependencyTypes`.`ID` = `PackageDepends`.`DepTypeID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT DISTINCT `PackageRelations`.`PackageID` AS `ID`,
                         `RelationTypes`.`Name` AS `Type`,
                         `PackageRelations`.`RelName` AS `Name`,
                         `PackageRelations`.`RelCondition` AS `Cond`
         FROM `PackageRelations`
         INNER JOIN `RelationTypes` ON `RelationTypes`.`ID` = `PackageRelations`.`RelTypeID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT DISTINCT `PackageGroups`.`PackageID` AS `ID`,
                         'Groups' AS `Type`,
                         `Groups`.`Name` AS `Name`,
                         '' AS `Cond`
         FROM `PackageGroups`
         INNER JOIN `Groups` ON `PackageGroups`.`GroupID` = `Groups`.`ID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT DISTINCT `PackageLicenses`.`PackageID` AS `ID`,
                         'License' AS `Type`,
                         `Licenses`.`Name` AS `Name`,
                         '' AS `Cond`
         FROM `PackageLicenses`
         INNER JOIN `Licenses` ON `PackageLicenses`.`LicenseID` = `Licenses`.`ID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT DISTINCT `Packages`.`ID` AS `ID`,
                         'Keywords' AS `Type`,
                         `PackageKeywords`.`Keyword` AS `Name`,
                         '' AS `Cond`
         FROM `PackageKeywords`
         INNER JOIN `Packages` ON `Packages`.`PackageBaseID` = `PackageKeywords`.`PackageBaseID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT DISTINCT `Packages`.`ID` AS `Packages_ID`,
                         'CoMaintainers' AS `Type`,
                         `Users`.`Username` AS `Name`,
                         '' AS `Cond`
         FROM `PackageComaintainers`
         INNER JOIN `Users` ON `Users`.`ID` = `PackageComaintainers`.`UsersID`
         INNER JOIN `Packages` ON `Packages`.`PackageBaseID` = `PackageComaintainers`.`PackageBaseID`
         ORDER BY `Name`)) AS anon_1
SQL: Sub-queries (after) 
SELECT anon_1.`ID` AS `anon_1_ID`,
       anon_1.`Type` AS `anon_1_Type`,
       anon_1.`Name` AS `anon_1_Name`,
       anon_1.`Cond` AS `anon_1_Cond`
FROM (
        (SELECT DISTINCT `PackageDepends`.`PackageID` AS `ID`,
                         `DependencyTypes`.`Name` AS `Type`,
                         `PackageDepends`.`DepName` AS `Name`,
                         `PackageDepends`.`DepCondition` AS `Cond`
         FROM `PackageDepends`
         INNER JOIN `DependencyTypes` ON `DependencyTypes`.`ID` = `PackageDepends`.`DepTypeID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT DISTINCT `PackageRelations`.`PackageID` AS `ID`,
                         `RelationTypes`.`Name` AS `Type`,
                         `PackageRelations`.`RelName` AS `Name`,
                         `PackageRelations`.`RelCondition` AS `Cond`
         FROM `PackageRelations`
         INNER JOIN `RelationTypes` ON `RelationTypes`.`ID` = `PackageRelations`.`RelTypeID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT `PackageGroups`.`PackageID` AS `ID`,
                         'Groups' AS `Type`,
                         `Groups`.`Name` AS `Name`,
                         '' AS `Cond`
         FROM `PackageGroups`
         INNER JOIN `Groups` ON `PackageGroups`.`GroupID` = `Groups`.`ID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT `PackageLicenses`.`PackageID` AS `ID`,
                         'License' AS `Type`,
                         `Licenses`.`Name` AS `Name`,
                         '' AS `Cond`
         FROM `PackageLicenses`
         INNER JOIN `Licenses` ON `PackageLicenses`.`LicenseID` = `Licenses`.`ID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT `Packages`.`ID` AS `ID`,
                         'Keywords' AS `Type`,
                         `PackageKeywords`.`Keyword` AS `Name`,
                         '' AS `Cond`
         FROM `PackageKeywords`
         INNER JOIN `Packages` ON `Packages`.`PackageBaseID` = `PackageKeywords`.`PackageBaseID`
         ORDER BY `Name`)
      UNION ALL
        (SELECT DISTINCT `Packages`.`ID` AS `Packages_ID`,
                         'CoMaintainers' AS `Type`,
                         `Users`.`Username` AS `Name`,
                         '' AS `Cond`
         FROM `PackageComaintainers`
         INNER JOIN `Users` ON `Users`.`ID` = `PackageComaintainers`.`UsersID`
         INNER JOIN `Packages` ON `Packages`.`PackageBaseID` = `PackageComaintainers`.`PackageBaseID`
         ORDER BY `Name`)) AS anon_1
SQL: Main-query (before) 
SELECT
	DISTINCT `Packages`.`ID` AS `Packages_ID`,
	`Packages`.`Name` AS `Packages_Name`,
	`PackageBases`.`ID` AS `PackageBaseID`,
	`PackageBases`.`Name` AS `PackageBase`,
	`Packages`.`Version` AS `Packages_Version`,
	`Packages`.`Description` AS `Packages_Description`,
	`Packages`.`URL` AS `Packages_URL`,
	`PackageBases`.`NumVotes` AS `PackageBases_NumVotes`,
	`PackageBases`.`Popularity` AS `PackageBases_Popularity`,
	`PackageBases`.`OutOfDateTS` AS `OutOfDate`,
	`Users`.`Username` AS `Maintainer`,
	`Users_1`.`Username` AS `Submitter`,
	`PackageBases`.`SubmittedTS` AS `FirstSubmitted`,
	`PackageBases`.`ModifiedTS` AS `LastModified`
FROM
	`Packages`
INNER JOIN `PackageBases` ON
	`PackageBases`.`ID` = `Packages`.`PackageBaseID`
LEFT OUTER JOIN `Users` ON
	`PackageBases`.`MaintainerUID` = `Users`.`ID`
LEFT OUTER JOIN `Users` AS `Users_1` ON
	`PackageBases`.`SubmitterUID` = `Users_1`.`ID`
WHERE
	`PackageBases`.`PackagerUID` IS NOT NULL
ORDER BY
	`PackageBases`.`Name`
SQL: Main-query (after) 
SELECT
	`Packages`.`ID` AS `Packages_ID`,
	`Packages`.`Name` AS `Packages_Name`,
	`PackageBases`.`ID` AS `PackageBaseID`,
	`PackageBases`.`Name` AS `PackageBase`,
	`Packages`.`Version` AS `Packages_Version`,
	`Packages`.`Description` AS `Packages_Description`,
	`Packages`.`URL` AS `Packages_URL`,
	`PackageBases`.`NumVotes` AS `PackageBases_NumVotes`,
	`PackageBases`.`Popularity` AS `PackageBases_Popularity`,
	`PackageBases`.`OutOfDateTS` AS `OutOfDate`,
	`Users`.`Username` AS `Maintainer`,
	`Users_1`.`Username` AS `Submitter`,
	`PackageBases`.`SubmittedTS` AS `FirstSubmitted`,
	`PackageBases`.`ModifiedTS` AS `LastModified`
FROM
	`Packages`
INNER JOIN `PackageBases` ON
	`PackageBases`.`ID` = `Packages`.`PackageBaseID`
LEFT OUTER JOIN `Users` ON
	`PackageBases`.`MaintainerUID` = `Users`.`ID`
LEFT OUTER JOIN `Users` AS `Users_1` ON
	`PackageBases`.`SubmitterUID` = `Users_1`.`ID`
WHERE
	`PackageBases`.`PackagerUID` IS NOT NULL
ORDER BY
	`PackageBases`.`Name`

Signed-off-by: moson-mo mo-son@mailbox.org

Edited by Mario Oenning

Merge request reports