Now I know that I can probably fix this by changing MySQL's config, however it made me look at the query being run and I'm just wondering why it's so verbose.
SELECT count(*)
FROM
SELECT "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."Priority", "SiteTree_Live"."ParentID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName"
FROM "SiteTree_Live"
LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN "Project_Live" ON "Project_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN Expertise_Projects ep ON ep.ProjectID = Project_Live.ID
LEFT JOIN Expertise e ON e.ID = ep.ExpertiseID AND e.DisciplineID = 2
LEFT JOIN Service_Projects sp ON sp.ProjectID = Project_Live.ID
LEFT JOIN Service s ON s.ID = sp.ServiceID AND s.DisciplineID = 2
WHERE ("SiteTree_Live"."ClassName" IN ('Project')) AND ("SiteTree_Live"."ParentID" = 18 AND (e.ID IS NOT NULL OR s.ID IS NOT NULL))
GROUP BY "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."Priority", "SiteTree_Live"."ParentID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END) all_distinct
I couldn't get the SQL to run (because of syntax error not sort buffer) in PHPMyAdmin until I took out the count and just ran
SELECT "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."Priority", "SiteTree_Live"."ParentID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName"
FROM "SiteTree_Live"
LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN "Project_Live" ON "Project_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN Expertise_Projects ep ON ep.ProjectID = Project_Live.ID
LEFT JOIN Expertise e ON e.ID = ep.ExpertiseID AND e.DisciplineID = 2
LEFT JOIN Service_Projects sp ON sp.ProjectID = Project_Live.ID
LEFT JOIN Service s ON s.ID = sp.ServiceID AND s.DisciplineID = 2
WHERE ("SiteTree_Live"."ClassName" IN ('Project')) AND ("SiteTree_Live"."ParentID" = 18 AND (e.ID IS NOT NULL OR s.ID IS NOT NULL))
GROUP BY "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."Priority", "SiteTree_Live"."ParentID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END
However even without the count it ran into the sort buffer so I changed it to
SELECT "SiteTree_Live"."*", Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName"
FROM "SiteTree_Live"
LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN "Project_Live" ON "Project_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN Expertise_Projects ep ON ep.ProjectID = Project_Live.ID
LEFT JOIN Expertise e ON e.ID = ep.ExpertiseID AND e.DisciplineID = 2
LEFT JOIN Service_Projects sp ON sp.ProjectID = Project_Live.ID
LEFT JOIN Service s ON s.ID = sp.ServiceID AND s.DisciplineID = 2
WHERE ("SiteTree_Live"."ClassName" IN ('Project')) AND ("SiteTree_Live"."ParentID" = 18 AND (e.ID IS NOT NULL OR s.ID IS NOT NULL))
GROUP BY "SiteTree_Live"."ID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END
The SQL above takes out the verbose columns and uses .* whicch runs fine.
So I'm wondering why does SS use the verbose column names? What was the rationale or issues that mean it was needed?
It seems to be the if you changed it to * then you might also get a bit more performance out it too.
Ran some very crude simple test on the below queries
SELECT `SiteTree_Live`.* FROM `SiteTree_Live`
SELECT `SiteTree_Live`.`ClassName`, `SiteTree_Live`.`Created`, `SiteTree_Live`.`LastEdited`, `SiteTree_Live`.`URLSegment`, `SiteTree_Live`.`Title`, `SiteTree_Live`.`MenuTitle`, `SiteTree_Live`.`Content`, `SiteTree_Live`.`MetaTitle`, `SiteTree_Live`.`MetaDescription`, `SiteTree_Live`.`MetaKeywords`, `SiteTree_Live`.`ExtraMeta`, `SiteTree_Live`.`ShowInMenus`, `SiteTree_Live`.`ShowInSearch`, `SiteTree_Live`.`HomepageForDomain`, `SiteTree_Live`.`ProvideComments`, `SiteTree_Live`.`Sort`, `SiteTree_Live`.`HasBrokenFile`, `SiteTree_Live`.`HasBrokenLink`, `SiteTree_Live`.`Status`, `SiteTree_Live`.`ReportClass`, `SiteTree_Live`.`CanViewType`, `SiteTree_Live`.`CanEditType`, `SiteTree_Live`.`ToDo`, `SiteTree_Live`.`Version`, `SiteTree_Live`.`Priority`, `SiteTree_Live`.`ParentID` FROM `SiteTree_Live`
The first ran in .0005 the second in .0006
Once again I'm wondering why does SS use the verbose column names? What was the rationale or issues that mean it was needed?