Bug Report: Inefficient join between sidecar tables (`vdiff` / `vdiff_tables`)

[arthurschreiber]

Overview of the Issue

There's quite a few queries where we join vdiff and vdiff_tables using a condition like vdiff.id = vdiff_tablet.vdiff_id.

Unfortunately, the data types between these two columns does not match (taken from release-21.0, but this is similar on main):

CREATE TABLE IF NOT EXISTS vdiff
(
    `id`                 bigint(20)   NOT NULL AUTO_INCREMENT,
    `vdiff_uuid`         varchar(64)  NOT NULL,
    `workflow`           varbinary(1024)       DEFAULT NULL,
    `keyspace`           varbinary(256)        DEFAULT NULL,
    `shard`              varchar(255) NOT NULL,
    `db_name`            varbinary(1024)       DEFAULT NULL,
    `state`              varbinary(64)         DEFAULT NULL,
    `options`            json                  DEFAULT NULL,
    `created_at`         timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `started_at`         timestamp    NULL     DEFAULT NULL,
    `liveness_timestamp` timestamp    NULL     DEFAULT NULL,
    `completed_at`       timestamp    NULL     DEFAULT NULL,
    `last_error`         varbinary(1024)      DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uuid_idx` (`vdiff_uuid`),
    KEY `state` (`state`),
    KEY `ks_wf_idx` (`keyspace`(64), `workflow`(64))
) ENGINE = InnoDB CHARSET = utf8mb4

CREATE TABLE IF NOT EXISTS vdiff_table
(
    `vdiff_id`      varchar(64)    NOT NULL,
    `table_name`    varbinary(128) NOT NULL,
    `state`         varbinary(64)           DEFAULT NULL,
    `lastpk`        varbinary(2000)         DEFAULT NULL,
    `table_rows`    bigint(20)     NOT NULL DEFAULT '0',
    `rows_compared` bigint(20)     NOT NULL DEFAULT '0',
    `mismatch`      tinyint(1)     NOT NULL DEFAULT '0',
    `report`        json                    DEFAULT NULL,
    `created_at`    timestamp      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    timestamp      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`vdiff_id`, `table_name`)
) ENGINE = InnoDB CHARSET = utf8mb4

This means we end up joining a bigint column against a varchar column, which prevents MySQL from using an index to do the join, and we end up having to scan the full vdiff_table contents to perform this join.

This causes performance issues, and some of these queries can even fail with errors like Out of sort memory, consider increasing server sort buffer size (errno 1038) if there's additional ORDER BY clauses on the queries.

Reproduction Steps

n/a

Binary Version

n/a

Operating System and Environment details

n/a

Log Fragments

n/a

[arthurschreiber]

Here's an example query:

select
    vd.id as vdiff_id,
    vdt.vdiff_id as vdiff_table_id,
    vdl.vdiff_id as vdiff_log_id
from
    _vt.vdiff as vd
    inner join _vt.vdiff_table as vdt on (vd.id = vdt.vdiff_id)
    inner join _vt.vdiff_log as vdl on (vd.id = vdl.vdiff_id)

And the json explain plan:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.05"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "vd",
          "access_type": "index",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "state",
          "used_key_parts": [
            "state"
          ],
          "key_length": "67",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.35",
            "data_read_per_join": "4K"
          },
          "used_columns": [
            "id"
          ]
        }
      },
      {
        "table": {
          "table_name": "vdt",
          "access_type": "index",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "vdiff_id",
            "table_name"
          ],
          "key_length": "388",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_index": true,
          "using_join_buffer": "hash join",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.70",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "vdiff_id"
          ],
          "attached_condition": "(cast(`_vt`.`vd`.`id` as double) = cast(`_vt`.`vdt`.`vdiff_id` as double))"
        }
      },
      {
        "table": {
          "table_name": "vdl",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_join_buffer": "hash join",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "1.05",
            "data_read_per_join": "24"
          },
          "used_columns": [
            "vdiff_id"
          ],
          "attached_condition": "(`_vt`.`vd`.`id` = `_vt`.`vdl`.`vdiff_id`)"
        }
      }
    ]
  }
}

The (cast(`_vt`.`vd`.`id` as double) = cast(`_vt`.`vdt`.`vdiff_id` as double)) part is where things go wrong.

[arthurschreiber]

This query actually shows there is a similar but different issue for the vdiff_log table, where we're missing an index on the vdiff_id column and end up doing a full table scan to join it. 🙈