How do I get MySQL to use a suitable index instead of a filesort for a window function?

Question

I am using MySQL version 8.0.38.

Given the following table:

CREATE TABLE `url_alias` (
    `pid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `src` VARCHAR(128) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
    `dst` VARCHAR(128) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
    PRIMARY KEY (`pid`) USING BTREE,
    INDEX `src_dst_pid` (`src`, `dst`, `pid` DESC) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
;

And the following query on that table:

SELECT *
FROM (
    SELECT ua.pid, ua.src, ua.dst, ROW_NUMBER() OVER w AS 'num'
    FROM url_alias ua
    WINDOW w AS (
        PARTITION BY ua.src, ua.dst 
        ORDER BY ua.pid DESC
    )
) t
WHERE t.num > 1

Using EXPLAIN FORMAT=JSON, I get the following information:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "85537.71"
    },
    "table": {
      "table_name": "t",
      "access_type": "ALL",
      "rows_examined_per_scan": 760313,
      "rows_produced_per_join": 253412,
      "filtered": "33.33",
      "cost_info": {
        "read_cost": "60196.48",
        "eval_cost": "25341.23",
        "prefix_cost": "85537.71",
        "data_read_per_join": "191M"
      },
      "used_columns": [
        "pid",
        "src",
        "dst",
        "num"
      ],
      "attached_condition": "(`t`.`num` > 1)",
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "838338.80"
          },
          "windowing": {
            "windows": [
              {
                "name": "w",
                "using_filesort": true,
                "filesort_key": [
                  "`src`",
                  "`dst`",
                  "`pid` desc"
                ],
                "functions": [
                  "row_number"
                ]
              }
            ],
            "cost_info": {
              "sort_cost": "760313.00"
            },
            "table": {
              "table_name": "ua",
              "access_type": "index",
              "key": "src_dst_pid",
              "used_key_parts": [
                "src",
                "dst",
                "pid"
              ],
              "key_length": "776",
              "rows_examined_per_scan": 760313,
              "rows_produced_per_join": 760313,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "1994.50",
                "eval_cost": "76031.30",
                "prefix_cost": "78025.80",
                "data_read_per_join": "568M"
              },
              "used_columns": [
                "pid",
                "src",
                "dst"
              ]
            }
          }
        }
      }
    }
  }
}

This shows that the window specified in the query is causing a filesort, even though ROW_NUMBER() OVER (PARTITION BY src, dst ORDER BY pid DESC) should be equivalent to reading the index src_dst_pid sequentially while maintaining some counter variable.

Is there anything that can be done to eliminate the filesort in the query, or is this some limitation of the query planner?

Answer 1

This appears to be a limitation of MySQL, as remarked by the MySQL team in the following bug report:

This is actually a problem that was discussed last year and it was concluded that with a current infrastructure , this optimisation is not possible.

https://bugs.mysql.com/bug.php?id=110999