Issue Bug Under Review

#102076PageSlugCandidateProvider puts a serious load on the database

Dmitry Dulepov 10/02/23, 08:56 updated 11/14/26, 11:07

Description

The query in is quite inefficient and stresses the database a lot:

        $statement = $queryBuilder
            ->select('uid', 'sys_language_uid', 'l10n_parent', 'l18n_cfg', 'pid', 'slug', 'mount_pid', 'mount_pid_ol', 't3ver_state', 'doktype', 't3ver_wsid', 't3ver_oid')
            ->from('pages')
            ->where(
                $queryBuilder->expr()->eq(
                    'sys_language_uid',
                    $queryBuilder->createNamedParameter($languageId, Connection::PARAM_INT)
                ),
                $queryBuilder->expr()->in(
                    'slug',
                    $queryBuilder->createNamedParameter(
                        $slugCandidates,
                        Connection::PARAM_STR_ARRAY
                    )
                )
            )
            // Exact match will be first, that's important
            ->orderBy('slug', 'desc')
            // versioned records should be rendered before the live records
            ->addOrderBy('t3ver_wsid', 'desc')
            // Sort pages that are not MountPoint pages before mount points
            ->addOrderBy('mount_pid_ol', 'asc')
            ->addOrderBy('mount_pid', 'asc')
            ->executeQuery();

MySql:

MariaDB [prod12]> explain SELECT `uid`, `sys_language_uid`, `l10n_parent`, `l18n_cfg`, `pid`, `slug`, `mount_pid`, `mount_pid_ol`, `t3ver_state`, `doktype`, `t3ver_wsid`, `t3ver_oid` FROM `pages` WHERE (`sys_language_uid` = ?) AND (`slug` IN (?)) AND (((`pages`.`deleted` = 0) AND (((`pages`.`t3ver_wsid` = 0) AND (`t3ver_state` <> 2))))) ORDER BY `slug` desc, `t3ver_wsid` desc, `mount_pid_ol` asc, `mount_pid` asc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?) AND (`slug` IN (?)) AND (((`pages`.`deleted` = 0) AND (((`pages`.`t3ver_ws...' at line 1
MariaDB [prod12]> explain SELECT `uid`, `sys_language_uid`, `l10n_parent`, `l18n_cfg`, `pid`, `slug`, `mount_pid`, `mount_pid_ol`, `t3ver_state`, `doktype`, `t3ver_wsid`, `t3ver_oid` FROM `pages` WHERE (`sys_language_uid` = 0) AND (`slug` IN ('')) AND (((`pages`.`deleted` = 0) AND (((`pages`.`t3ver_wsid` = 0) AND (`t3ver_state`
<> 2))))) ORDER BY `slug` desc, `t3ver_wsid` desc, `mount_pid_ol` asc, `mount_pid` asc;
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                       |
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
|    1 | SIMPLE      | pages | ref  | slug          | slug | 384     | const | 2    | Using where; Using filesort |
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
1 row in set (0.000 sec)

So where + filesort.

For now I was able to fix this by adjusting slug key like this:

alter table pages add key slug2 (slug(127),`t3ver_wsid`,`mount_pid_ol`,`mount_pid`);

Check the result:

MariaDB [prod12]> explain SELECT `uid`, `sys_language_uid`, `l10n_parent`, `l18n_cfg`, `pid`, `slug`, `mount_pid`, `mount_pid_ol`, `t3ver_state`, `doktype`, `t3ver_wsid`, `t3ver_oid` FROM `pages` WHERE (`sys_language_uid` = 0) AND (`slug` IN ('')) AND (((`pages`.`deleted` = 0) AND (((`pages`.`t3ver_wsid` = 0) AND (`t3ver_state`
<> 2))))) ORDER BY `slug` desc, `t3ver_wsid` desc, `mount_pid_ol` asc, `mount_pid` asc;
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|    1 | SIMPLE      | pages | range | slug,slug2    | slug2 | 388     | NULL | 2    | Using where |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.001 sec)

No filesort.

Reviews 2

History 1318

  1. Oliver Hader commented <span data-processor="relativetime" data-value="2023-10-04T14:19:55+00:00" > 10/04/23, 14:19 </span>

    Applying the change to TYPO3 v12, I’m getting the following (with MySQL 8.0.34):

    mysql> EXPLAIN SELECT `uid`, `sys_language_uid`, `l10n_parent`, `l18n_cfg`, `pid`, `slug`, `mount_pid`, `mount_pid_ol`, `t3ver_state`, `doktype`, `t3ver_wsid`, 
    `t3ver_oid` FROM `pages` WHERE (`sys_language_uid` = 0) AND (`slug` IN ('a','b','c')) AND (((`pages`.`deleted` = 0) AND (((`pages`.`t3ver_wsid` = 0) AND 
    (`t3ver_state` <> 2))))) ORDER BY `slug` desc, `t3ver_wsid` desc, `mount_pid_ol` asc, `mount_pid` asc;
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
    |  1 | SIMPLE      | pages | NULL       | range | slug          | slug | 515     | NULL |    3 |     1.67 | Using index condition; Using where; Using filesort |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

    In the initial report "So where + filesort." is mentioned – it sounds, like this would be a bad thing?

    The query below “check result” in the report above is using a static `slug` IN ('') which leads to not having to sort. However, any other real scenario with more than one given slug item has to be sorted.

    Anyway, I think there’s more that can be done here in order to optimize the query any have some numbers on it.

  2. Dmitry Dulepov commented <span data-processor="relativetime" data-value="2023-10-04T14:35:59+00:00" > 10/04/23, 14:35 </span>

    It is still better and faster. Read: https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

    But feel free to close the report. I guess it is easier to keep it as is. I will simply have the change locally.

  3. Markus Klein commented <span data-processor="relativetime" data-value="2023-10-04T15:24:17+00:00" > 10/04/23, 15:24 </span>

    If you preserve the original index too, does it still need to filesort for the query @ohader ?
    (That would mean that MySQL is not able to pick up the new index, if it only needs slug for sorting)

  4. Markus Klein commented <span data-processor="relativetime" data-value="2023-10-04T15:29:36+00:00" > 10/04/23, 15:29 </span>

    Markus Klein wrote in #note-4:
    > If you preserve the original index too, does it still need to filesort for the query @ohader ?
    > (That would mean that MySQL is not able to pick up the new index, if it only needs slug for sorting)

    Nope, it does not make a difference.

  5. Oliver Hader commented <span data-processor="relativetime" data-value="2023-10-04T15:52:32+00:00" > 10/04/23, 15:52 </span>

    Using EXPLAIN FORMAT=JSON ... provides some more details (having the patch with the modified slug index applied):

    Query

    EXPLAIN FORMAT=JSON SELECT `uid`, `sys_language_uid`, `l10n_parent`, `l18n_cfg`, `pid`, `slug`, `mount_pid`, `mount_pid_ol`, `t3ver_state`, `doktype`, `t3ver_wsid`, `t3ver_oid` FROM `pages` WHERE (`sys_language_uid` = 0) AND (`slug` IN ('/','/content-examples','/content-examples/', '/content-examples/menus', '/content-examples/menus/')) AND (((`pages`.`deleted` = 0) AND (((`pages`.`t3ver_wsid` = 0) AND (`t3ver_state` <> 2))))) ORDER BY `slug` desc, `t3ver_wsid` desc, `mount_pid_ol` asc, `mount_pid` asc \G

    Result

    EXPLAIN: {
      "query_block": {
        "select_id": 1,
        "cost_info": {
          "query_cost": "3.57"
        },
        "ordering_operation": {
          "using_filesort": true,
          "cost_info": {
            "sort_cost": "0.06"
          },
          "table": {
            "table_name": "pages",
            "access_type": "range",
            "possible_keys": [
              "slug"
            ],
            "key": "slug",
            "used_key_parts": [
              "slug",
              "t3ver_wsid"
            ],
            "key_length": "515",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 0,
            "filtered": "1.18",
            "index_condition": "(`t3_ip13_local`.`pages`.`t3ver_wsid` = 0)",
            "cost_info": {
              "read_cost": "3.50",
              "eval_cost": "0.01",
              "prefix_cost": "3.51",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "uid",
              "pid",
              "deleted",
              "sys_language_uid",
              "l10n_parent",
              "t3ver_oid",
              "t3ver_wsid",
              "t3ver_state",
              "doktype",
              "mount_pid",
              "mount_pid_ol",
              "slug",
              "l18n_cfg"
            ],
            "attached_condition": "((`t3_ip13_local`.`pages`.`deleted` = 0) and (`t3_ip13_local`.`pages`.`sys_language_uid` = 0) and (`t3_ip13_local`.`pages`.`slug` in ('/','/content-examples','/content-examples/','/content-examples/menus','/content-examples/menus/')) and (`t3_ip13_local`.`pages`.`t3ver_state` <> 2))"
          }
        }
      }
    }

    Comparison of what the patch changed in the EXPLAIN statement (basically diff -u before.json after.json):

    @@ -17,12 +17,14 @@
             ],
             "key": "slug",
             "used_key_parts": [
    -          "slug"
    +          "slug",
    +          "t3ver_wsid"
             ],
    -        "key_length": "511",
    +        "key_length": "515",
             "rows_examined_per_scan": 5,
             "rows_produced_per_join": 0,
             "filtered": "1.18",
    +        "index_condition": "(`t3_ip13_local`.`pages`.`t3ver_wsid` = 0)",
             "cost_info": {
               "read_cost": "3.50",
               "eval_cost": "0.01",
    @@ -44,7 +46,7 @@
               "slug",
               "l18n_cfg"
             ],
    -        "attached_condition": "((`t3_ip13_local`.`pages`.`t3ver_wsid` = 0) and (`t3_ip13_local`.`pages`.`deleted` = 0) and (`t3_ip13_local`.`pages`.`sys_language_uid` = 0) and (`t3_ip13_local`.`pages`.`slug` in ('/','/content-examples','/content-examples/','/content-examples/menus','/content-examples/menus/')) and (`t3_ip13_local`.`pages`.`t3ver_state` <> 2))"
    +        "attached_condition": "((`t3_ip13_local`.`pages`.`deleted` = 0) and (`t3_ip13_local`.`pages`.`sys_language_uid` = 0) and (`t3_ip13_local`.`pages`.`slug` in ('/','/content-examples','/content-examples/','/content-examples/menus','/content-examples/menus/')) and (`t3_ip13_local`.`pages`.`t3ver_state` <> 2))"
           }
         }
       }
    • the query on t3ver_wsid can be served by index (ICP for that “partition”)
    • key size grew by 4 bytes (that’s the size of the t3ver_wsid field as INT)
    • still having the filesort on the ordering part
  6. Markus Klein commented <span data-processor="relativetime" data-value="2023-10-05T06:35:07+00:00" > 10/05/23, 06:35 </span>

    Dmitry Dulepov wrote in #note-3:
    > It is still better and faster. Read: https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html
    >
    > But feel free to close the report. I guess it is easier to keep it as is. I will simply have the change locally.

    Can you please verify on your systems the last query example from Olly? Is filesort gone for you on your server?

  7. Dmitry Dulepov commented <span data-processor="relativetime" data-value="2023-10-05T07:46:10+00:00" > 10/05/23, 07:46 </span>

    WIth this query filesort is there but it uses index condition. So it is still an improvement.

  8. Tymoteusz Motylewski commented <span data-processor="relativetime" data-value="2023-10-09T09:09:04+00:00" > 10/09/23, 09:09 </span>

    fyi, i have linked issue https://forge.typo3.org/issues/95690 which has some work done in a similar area (code changes and test environment to reproduce, and traces), maybe it’s useful.

  9. Markus Klein changed status Under Review New removed the assignee Complexity easy <span data-processor="relativetime" data-value="2024-09-10T18:23:59+00:00" > 09/10/24, 18:23 </span>

  10. Georg Ringer commented <span data-processor="relativetime" data-value="2026-09-07T21:16:35+00:00" > 09/07/26, 21:16 </span>

    (AI based)

    Still valid on current main (15.0-dev): the query in PageSlugCandidateProvider
    and “KEY slug (slug(127))” are unchanged since the report.

    I measured the proposed index change on MariaDB 10.11 with a 1265 row pages
    table, using the same query shape, with the composite index
    (slug(127), t3ver_wsid, mount_pid_ol, mount_pid) from the abandoned patch:

    current state                     key: slug            Using where; Using filesort
    composite index added additionally key: slug (!) Using where; Using filesort
    composite index with FORCE INDEX key: composite Using index condition; Using where; Using filesort

    That answers the two questions this issue got stuck on:

    • mklein asked whether the filesort remains if the original index is kept.
      It does - and worse, the optimizer does not even consider the new index while
      the plain slug index exists. That is why the patch had to replace the existing
      index rather than add a second one. *
      dmitryd’s summary (“filesort is there but it uses index condition, so it is
      still an improvement”) is confirmed. The gain is index condition pushdown, not
      the removal of the sort.

    What was never stated in the discussion, and is the actual reason the sort
    cannot be optimized away: pages.slug is a TEXT column. MySQL and MariaDB can
    only index TEXT with a prefix, and a prefix index can never satisfy an ORDER BY,
    because it does not carry the full values. So the filesort on slug is
    structural – no amount of trailing columns in the index changes it. On top of
    that the ORDER BY mixes directions (slug DESC, t3ver_wsid DESC, mount_pid_ol
    ASC, mount_pid ASC), which would need direction-matching index parts even with a
    full-length index.

    Getting rid of the filesort therefore means changing the column type of slug
    from text to a bounded varchar – a much larger, breaking discussion, not an
    index tweak.

    One unrelated observation while looking at this: since commit fe4c7a80c6c
    (“[FEATURE] Add expression language support for limitToPages”, March 2026) the
    query selects “*” instead of the explicit twelve column list quoted in the
    report. Restoring an explicit column list would reduce the row width
    independently of any index work, provided limitToPages does not need the extra
    columns.

    Suggestion: do not close this. Change 81309 was abandoned by its own author with
    “No conclusion so far” after it had already received a +1, so the idea was not
    rejected, the discussion just stalled. Index condition pushdown is a real if
    modest gain and could be revisited on that basis.