Skip to content

PageTable fields with sortfields causes N+1 SQL queries  #1971

@tuomassalo

Description

@tuomassalo

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

  1. Add a template, e.g. mytpl
  2. To mytpl, add text field mytext
  3. Install the PageTable modules
  4. Create a PageTable field ptfield
  5. On Details tab, set Sort fields to mytext
  6. Add a PageTable field ptfield to basic-page
  7. Edit a basic page: add a few subpages to ptfield. Enter something to mytext for each one.
  8. Run this code:
$page = $wire->pages->get("id=1015"); // replace with the page id
$foo = $page->ptfield;

$queries = $wire->database->getQueryLog();
echo '<p>Number queries: ' . count($queries) . '</p>';
foreach($queries as $query){
    echo '<p>' . htmlspecialchars($query) . '</p>';
}
  1. Observe that for each subpage, the output has a line like:
SELECT field_mytext.data AS `mytext__data` FROM `field_mytext` WHERE field_mytext.pages_id=1021

Setup/Environment

  • ProcessWire version: latest dev branch, vanilla installation
  • (Optional) PHP version: 8.2
  • (Optional) MySQL version: MariaDB 10.5

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions