Actions
Bug #102076
openPageSlugCandidateProvider puts a serious load on the database
Status:
Under Review
Priority:
Should have
Assignee:
Category:
Site Handling, Site Sets & Routing
Target version:
-
Start date:
2023-10-02
Due date:
% Done:
0%
Estimated time:
TYPO3 Version:
12
PHP Version:
8.1
Tags:
Complexity:
easy
Is Regression:
Sprint Focus:
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.
Actions