Full table scan against table router

Show the output from DESCRIBE router and SHOW INDEX FROM router, so we can be sure the site doesn't have modifications. The defaults are as follows:

> DESCRIBE router;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(255) | NO   | PRI |         |       |
| path            | varchar(255) | NO   |     |         |       |
| pattern_outline | varchar(255) | NO   | MUL |         |       |
| fit             | int(11)      | NO   |     | 0       |       |
| route           | longblob     | YES  |     | NULL    |       |
| number_parts    | smallint(6)  | NO   |     | 0       |       |
| alias           | varchar(255) | YES  | MUL | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
7 rows in set (0.002 sec)

> SHOW INDEX FROM router;
+--------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+--------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| router |          0 | PRIMARY               |            1 | name            | A         |         332 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| router |          1 | pattern_outline_parts |            1 | pattern_outline | A         |         332 |      191 | NULL   |      | BTREE      |         |               | NO      |
| router |          1 | pattern_outline_parts |            2 | number_parts    | A         |         332 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| router |          1 | alias                 |            1 | alias           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+--------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
4 rows in set (0.001 sec)

The query you posted does cause a full table scan on a default site:

> EXPLAIN SELECT pattern_outline FROM router WHERE '/node/278960' LIKE CONCAT(pattern_outline, '%') AND pattern_outline != '/';
+------+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys         | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | router | ALL  | pattern_outline_parts | NULL | NULL    | NULL | 332  | Using where |
+------+-------------+--------+------+-----------------------+------+---------+------+------+-------------+

It would be good to know if Drupal Core code is initiating that query or if it comes from a contributed module. If we knew which action on the website induces that query to execute on a plain Drupal site it would really help.

When MySQL will full table scan with LIKE clauses is up to the query optimizer. We definitely need to understand what code calls this query and in what situations.

If this problem was discovered on a version of Drupal that is not 11.x, add that information in the issue summary and leave the version at 11.x. In Drupal core changes are made on on 11.x (our main development branch) first, and are then back ported as needed according to the Core change policies → . Also mentioned on the version → section of the list of issue fields documentation.

SELECT pattern_outline FROM router WHERE '/node/278960' LIKE CONCAT(pattern_outline, '%') AND pattern_outline != '/';

This doesn't come from core, the core query against pattern_outline is:

      $routes = $this->connection->query("SELECT [name], [route], [fit] FROM {" . $this->connection->escapeTable($this->tableName) . "} WHERE [pattern_outline] IN ( :patterns[] ) AND    [number_parts] >= :count_parts", [

You could grep the contrib modules you have installed for CONCAT to try to find it.

Thank you so much for your help.
Actually, I spoke with the developers and it seems this is custom code.
They will fix the issue.
Thanks a lot again

It comes from the Fast404 module: https://git.drupalcode.org/project/fast_404/-/blob/8.x-3.x/src/Fast404.p...

Moving to the appropriate place.

From the project's page: "The module is in a very stable place, though we are doing performance enhancements to the path checking".

That comment almost certainly relates to the D6 version! https://www.drupal.org/node/1036382/revisions/view/1712350/7842397 →

On a large site with lots of content, this has the potential to overload PHP-FPM and bring the site down. Ask me how I know...

Bumping this up to major. PHP-FPM has been locking up for ages now, I've only just realised it's because of fast404.

I haven't used this module.

Given that 404 pages can (usually) be cached in the dynamic_page_cache as a single entry, and should be very quick to serve, has anyone checked recently that it actually makes things any faster, rather than just adding slow queries?