Open tuomassalo opened 2 months ago
I'll sometimes aim for more simple 1-index select queries rather than a single query with multiple joins because the multiple simple queries can often consume less time than the single more complex query. Though it depends on the case, and I don't remember for this case specifically. Though the mentioned query is just the sort of type that might perform more quickly as independent queries than in a larger query, though can't say for certain without benchmarking. When it comes to sorting, performance wise it's preferable to sort on a native pages table property when possible, such as name, sort, created, modified, id, etc.
About performance and prefething:
I've done some benchmarking by fetching pages with 10 or even 50 joined fields. Even with low network latencies (docker on local dev machine), using joins seems to perform about three times faster.
$pageIds = [/* list of 100 pages */];
$fields = [/* list of 50 basic text fields */];
foreach($pageIds as $pageId){
$page = $wire->pages->get("id=$pageId", ["loadOptions" => ["joinFields" => $fields]]);
// vs.
// $page = $wire->pages->get("id=$pageId");
foreach($fields as $field){
$foo = $page->get($field);
}
}
I might be missing something in my perf tests, but, with any number of fields, I would be surprised to find a common scenario where the database was slower to perform one SELECT
with joins than N+1 simpler SELECT
clauses. After all, EXPLAIN
looks very good for the join selects that PW generates.
One concern about my PR, though: am I correct that setting joinFields
in getById()
ignores any autojoin fields that the template might have, thus actually (potentially) increasing the total number of queries, if those fields are queried later?
Thus, I believe the patch should ideally merge sortfields
and autojoin
fields, if the template has that set.
Short description of the issue
Bad performance: if a PageTable field has
sortfields
, the sorting makes an SQL query (or more) for each subpage.Expected behavior
Fetching a PageTable field does not cause a big number of unnecessary SQL queries.
Actual behavior
For each subpage, the sort algorithm queries each sortfield from on the fly. If a PageTable has 50 subpages and three sort fields, this means 150 extra SQL queries.
Optional: Suggestion for a possible fix
PR coming up.
Steps to reproduce the issue
mytpl
mytpl
, add text fieldmytext
ptfield
Details
tab, setSort fields
tomytext
ptfield
tobasic-page
ptfield
. Enter something tomytext
for each one.Setup/Environment