TYPO3 Core

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.

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.

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

Markus Klein wrote in #note-4:

If you preserve the original index too, does it still need to filesort for the query @Oliver Hader ?
(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.

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

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?

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

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.