I am working on a Silverstripe custom report which had been created before my time at my current employer. The report returns CompetitionEntry rows for a specific Competition along with the title of the competition (and a couple of other fields which I have removed to keep the code snippets tidy).
When we tried to export 10,000 or more results, it would timeout. The MySQL general log showed that the query running was looking up the competition each time for every single competition entry:
SELECT DISTINCT Created, LastEdited, URLSegment, Title, MenuTitle, Content, MetaDescription, ExtraMeta, ShowInMenus, ShowInSearch, Sort, HasBrokenFile, HasBrokenLink, ReportClass, CanViewType, CanEditType, Priority, ProvideComments, MetaTitle, CommentNotificationEmail, Version, ParentID, TrackingTagName, CustomQuestion, EnableCustomQuestion, CustomQuestionRequired, TermsAndConditions, SubmittedText, HeaderContent, InactiveContent, BackgroundColour, Active, Image1ID, Image2ID, CASE WHEN ClassName IS NOT NULL THEN ClassName ELSE 'SiteTree' END AS RecordClassName
FROM SiteTree_Live
LEFT JOIN Page_Live ON Page_Live.ID = SiteTree_Live.ID
LEFT JOIN CompetitionPage_Live ON CompetitionPage_Live.ID = SiteTree_Live.ID
WHERE (SiteTree_Live.ID = 308) AND (SiteTree_Live.ClassName IN ('CompetitionPage'))
ORDER BY Active DESC
LIMIT 1
Columns function:
public function columns() {
return array(
"Competition.Title" => "Competition",
"FirstName" => "First name",
"LastName" => "Last name",
"Email" => "Email"
);
}
My getExportFields:
public function getExportFields(){
return array(
"Competition.Title" => "Competition",
"Date" => "Date",
"FirstName" => "FirstName",
"LastName" => "LastName",
"Email" => "Email",
);
}
By commenting out the reference to Competition.Title the query runs quickly and gives me all of the entries.
I cannot see how to make the query work efficiently, or if it's possible to just lookup the competition once, and then add the title (and one or two further competition fields) to each competition entry.