#102076PageSlugCandidateProvider puts a serious load on the database
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
Related issues 2
History 1318
-
Gerrit Code Review pushed patch set 1 for main to change 81309 status
NewUnder Review <span data-processor="relativetime" data-value="2023-10-02T10:51:13+00:00" > 10/02/23, 10:51 </span> -
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.
-
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) -
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.
-
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 modifiedslugindex 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 \GResult
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
EXPLAINstatement (basicallydiff -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_wsidcan be served by index (ICP for that “partition”) - key size grew by 4 bytes (that’s the size of the
t3ver_wsidfield asINT) - still having the
filesorton the ordering part
- the query on
-
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?
-
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.
-
Tymoteusz Motylewski changed Related to #95690 <span data-processor="relativetime" data-value="2023-10-09T09:06:40+00:00" > 10/09/23, 09:06 </span>
-
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.
-
Stefan Bürk changed assigned to Stefan Bürk <span data-processor="relativetime" data-value="2024-01-16T02:40:48+00:00" > 01/16/24, 02:40 </span>
-
Markus Klein changed status
Under ReviewNew removed the assignee Complexityeasy<span data-processor="relativetime" data-value="2024-09-10T18:23:59+00:00" > 09/10/24, 18:23 </span> -
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 filesortThat answers the two questions this issue got stuck on:
mklein asked whether the filesort remains if the original index is kept.dmitryd’s summary (“filesort is there but it uses index condition, so it is
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. *
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. -
Gerrit Code Review pushed patch set 2 for main to change 81309 status
NewUnder Review <span data-processor="relativetime" data-value="2026-09-08T08:16:20+00:00" > 09/08/26, 08:16 </span> -
Gerrit Code Review pushed patch set 3 for main to change 81309 <span data-processor="relativetime" data-value="2026-09-08T08:18:09+00:00" > 09/08/26, 08:18 </span>
-
Alexander Schnitzler changed Related to #111392 <span data-processor="relativetime" data-value="2026-11-10T07:38:29+00:00" > 11/10/26, 07:38 </span>
-
Gerrit Code Review pushed patch set 4 for main to change 81309 <span data-processor="relativetime" data-value="2026-11-14T07:50:56+00:00" > 11/14/26, 07:50 </span>
-
Gerrit Code Review pushed patch set 5 for main to change 81309 <span data-processor="relativetime" data-value="2026-11-14T11:07:29+00:00" > 11/14/26, 11:07 </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 givenslugitem 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.