Analyzing a SQL statement using VEXPLAIN

Analyzing a SQL statement using VTEXPLAIN >>

Introduction #

`QUERIES` Type #

The QUERIES format returns an output similar to what the command line application vtexplain returns - a list of the queries that have been run on MySQL, and against which shards they were issued.

How it works #

Unlike normal EXPLAIN queries, VEXPLAIN QUERIES actually runs your query, and logs the interactions with the tablets. After running your query using this extra logging, the result you get is a table with all the interactions listed.

How to read the output #

The output has four columns:

The first column, # groups queries that were sent in a single call together.
Keyspace - which keyspace was this query sent to.
Shard - for sharded keyspaces, this column will show which shard a query is sent to.
Query - the actual query used.

Example 1: #

mysql> vexplain queries select * from user where id = 4;
+------+----------+-------+-----------------------------------------------------------+
| #    | keyspace | shard | query                                                     |
+------+----------+-------+-----------------------------------------------------------+
|    0 | ks       | c0-   | select id, lookup, lookup_unique from `user` where id = 4 |
+------+----------+-------+-----------------------------------------------------------+
1 row in set (0.00 sec)

Here we have a query where the planner can immediately see which shard to send the query to.

Example 2: #

mysql> vexplain queries select * from user where lookup = 'apa';
+------+----------+-------+-------------------------------------------------------------------+
| #    | keyspace | shard | query                                                             |
+------+----------+-------+-------------------------------------------------------------------+
|    0 | ks       | -40   | select lookup, keyspace_id from lookup where lookup in ('apa')    |
|    1 | ks       | c0-   | select id, lookup, lookup_unique from `user` where lookup = 'apa' |
|    2 | ks       | 40-80 | select id, lookup, lookup_unique from `user` where lookup = 'apa' |
+------+----------+-------+-------------------------------------------------------------------+
3 rows in set (0.02 sec)

This is a query where the planner has to do a vindex lookup to find which shard the data might live on.

`PLAN` Type #

The PLAN format returns the vtgate plan for the given query. It does so without actually running any queries - it just plans the given query and presents the plan.

How to read the output #

The output contains a scalar output having a JSON description of the plan that vtgate will use for the query.

Example: #

mysql> vexplain plan select * from corder join commerce.product as prod on corder.sku = prod.sku;

{
  "OperatorType": "Join",
  "Variant": "Join",
  "JoinColumnIndexes": "L:1,L:2,L:3,L:4,R:0,R:1,R:2",
  "JoinVars": {
    "corder_sku": 0
  },
  "TableName": "corder_product",
  "Inputs": [
    {
      "OperatorType": "Route",
      "Variant": "Scatter",
      "Keyspace": {
        "Name": "customer",
        "Sharded": true
      },
      "FieldQuery": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder where 1 != 1",
      "Query": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder",
      "Table": "corder"
    },
    {
      "OperatorType": "Route",
      "Variant": "Unsharded",
      "Keyspace": {
        "Name": "commerce",
        "Sharded": false
      },
      "FieldQuery": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where 1 != 1",
      "Query": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where prod.sku = :corder_sku",
      "Table": "product"
    }
  ]
}

In this example, we are executing a cross-keyspace join between two tables. The corder table living in the customer keyspace and product table living in the commerce keyspace.

`ALL` Type #

The ALL format returns the vtgate plan along with the MySQL explain output for the executed queries.

How to read the output #

The output contains a scalar output having a JSON description of the plan that vtgate will use for the query annotated with the explain output from mysql for these queries.

Example: #

mysql> vexplain all select * from corder join commerce.product as prod on corder.sku = prod.sku;

{
  "OperatorType": "Join",
  "Variant": "Join",
  "JoinColumnIndexes": "L:1,L:2,L:3,L:4,R:0,R:1,R:2",
  "JoinVars": {
    "corder_sku": 0
  },
  "TableName": "corder_product",
  "Inputs": [
    {
      "OperatorType": "Route",
      "Variant": "Scatter",
      "Keyspace": {
        "Name": "customer",
        "Sharded": true
      },
      "FieldQuery": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder where 1 != 1",
      "Query": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder",
      "Table": "corder",
      "mysql_explain_json": {
        "query_block": {
          "select_id": 1,
          "cost_info": {
            "query_cost": "0.65"
          },
          "table": {
            "table_name": "corder",
            "access_type": "ALL",
            "rows_examined_per_scan": 4,
            "rows_produced_per_join": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "0.40",
              "prefix_cost": "0.65",
              "data_read_per_join": "640"
            },
            "used_columns": [
              "order_id",
              "customer_id",
              "sku",
              "price"
            ]
          }
        }
      }
    },
    {
      "OperatorType": "Route",
      "Variant": "Unsharded",
      "Keyspace": {
        "Name": "commerce",
        "Sharded": false
      },
      "FieldQuery": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where 1 != 1",
      "Query": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where prod.sku = :corder_sku",
      "Table": "product",
      "mysql_explain_json": {
        "query_block": {
          "select_id": 1,
          "cost_info": {
            "query_cost": "1.00"
          },
          "table": {
            "table_name": "prod",
            "access_type": "const",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "sku"
            ],
            "key_length": "130",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "0.10",
              "prefix_cost": "0.00",
              "data_read_per_join": "272"
            },
            "used_columns": [
              "sku",
              "description",
              "price"
            ]
          }
        }
      }
    }
  ]
}

This example uses the same query as the previous ones. For all the Route operators, we are annotating them with the MySQL explain output for the query that the route is executing.

`MYSQLPLAN` Type #

The MYSQLPLAN format runs MySQL's EXPLAIN FORMAT=JSON against the shards a SELECT would target. It never executes the wrapped query. Each shard's MySQL plan is attached to the corresponding Route node in the vtgate plan tree. This type is new in Vitess v25.0.

How it works #

VEXPLAIN MYSQLPLAN builds the real vtgate plan. For each Route, it resolves the target shards using only the vindex and topology metadata — it never queries a tablet or reads table data to do this. It then issues EXPLAIN FORMAT=JSON against every resolved shard; the wrapped query itself is never run. The per-shard MySQL plan is attached to the plan tree keyed by shard, so per-shard plan and cost differences are visible.

Compared with the two closest modes on this page:

PLAN: returns the plan tree only — no MySQL explain and no resolved shards.
ALL: attaches MySQL explain but executes the query to discover the shard-level queries, and reports only one shard's plan per primitive.
MYSQLPLAN: never runs the wrapped query, and reports every resolved shard separately.

How to read the output #

The output contains a scalar output having a JSON description of the vtgate plan. Under MYSQLPLAN, each Route node carries mysql_explain_json_by_shard, a map keyed by shard name with one MySQL EXPLAIN FORMAT=JSON plan per resolved shard. VEXPLAIN ALL instead carries mysql_explain_json, a single object per primitive. MYSQLPLAN and ALL therefore use two separate keys, each with its own shape.

Example: #

mysql> vexplain mysqlplan select id from user;

{
  "OperatorType": "Route",
  "Variant": "Scatter",
  "Keyspace": {
    "Name": "ks",
    "Sharded": true
  },
  "FieldQuery": "select id from `user` where 1 != 1",
  "Query": "select id from `user`",
  "Table": "user",
  "mysql_explain_json_by_shard": {
    "-40": { "query_block": { ... } },
    "40-80": { "query_block": { ... } },
    "80-c0": { "query_block": { ... } },
    "c0-": { "query_block": { ... } }
  }
}

In this example the query scatters across all four shards; the routing signal is the "Variant": "Scatter" field shown earlier in the same JSON, distinct from the mysql_explain_json_by_shard map, which only shows what MySQL did on each shard. Each shard's MySQL plan appears separately under mysql_explain_json_by_shard, keyed by shard name. Each shard's value is a full EXPLAIN FORMAT=JSON query_block object, the same shape as the ALL example above, so the { ... } placeholders here stand in for that object.

Supported and unsupported queries #

VEXPLAIN MYSQLPLAN supports only SELECT statements whose target shards can be resolved from a vindex without reading cluster data (a lookup vindex, which requires a mapping-table read, does not qualify — see below).

Even though MYSQLPLAN never runs the wrapped query, MySQL itself executes the per-shard EXPLAIN FORMAT=JSON. That plan can materialize a derived table or a view, which is why the shapes that rely on those are rejected.

Some query shapes are rejected at plan time. For these, use VEXPLAIN ALL instead:

DML (INSERT/UPDATE/DELETE) is rejected with VEXPLAIN MYSQLPLAN only supports SELECT statements; use VEXPLAIN ALL instead.
A SELECT whose shard set depends on data — such as a cross-shard join, a subquery, or a lookup vindex — is rejected with VEXPLAIN MYSQLPLAN cannot resolve the target shards without executing the query (the query uses a cross-shard join, subquery, or lookup vindex); use VEXPLAIN ALL instead.
A SELECT that reads a derived table or a view is rejected with VEXPLAIN MYSQLPLAN does not support derived tables or views, because EXPLAIN FORMAT=JSON can materialize a derived table during optimization - running any stored function inside it once per shard - which would violate MYSQLPLAN's promise never to run the wrapped query; use VEXPLAIN ALL instead.
A SELECT SQL_CALC_FOUND_ROWS with a LIMIT that also uses GROUP BY or HAVING is rejected with VEXPLAIN MYSQLPLAN does not support SELECT SQL_CALC_FOUND_ROWS with GROUP BY or HAVING, because the planner rewrites the row count into a derived table that EXPLAIN could materialize; use VEXPLAIN ALL instead.

Three shapes are rejected with no VEXPLAIN ALL fallback, because running the query (which is what VEXPLAIN ALL does) would have a side effect:

A sequence next-value query, which would consume a sequence value.
A query that calls an advisory-lock function: get_lock, release_lock, release_all_locks, is_free_lock, or is_used_lock.
A query issued on a reserved connection, for example after creating a temporary table.

When to use MYSQLPLAN #

Use vexplain mysqlplan to see MySQL's chosen index and join plan for each shard of a SELECT, without paying the cost or side effects of running it. For example, use it to spot a single skewed shard whose optimizer statistics produce a worse plan than its peers. MYSQLPLAN shows what MySQL does on each shard, not why vtgate routed the query there: the plan's Variant field (for example Scatter) shows the routing, and VEXPLAIN PLAN shows the full routing plan. To narrow a query that scatters, see the query-serving metrics and vindex hints guides.

`TRACE` Type #

The TRACE format provides a detailed execution trace of the query, showing how it's processed through various operators and interactions with tablets.

How it works #

VEXPLAIN TRACE runs the query and logs all interactions between the operators and the tablets. VExplain returns a single row with a single column containing the execution plan in JSON format, including all the interactions between the operators and the tablets.

How to read the output #

The output is a JSON representation of the query execution plan, with additional fields for each operator:

NoOfCalls: Number of times the operator was invoked
AvgNumberOfRows: Average number of rows processed by the operator
MedianNumberOfRows: Median number of rows processed by the operator
ShardsQueried: (For operators interacting with vttablets) Number of shards queried

Example: #

mysql> vexplain trace select ... ;

{
  "OperatorType": "Projection",
  "NoOfCalls": 1,
  "AvgNumberOfRows": 5,
  "MedianNumberOfRows": 5,
  "Inputs": [
    {
      "OperatorType": "Aggregate",
      "Variant": "Ordered",
      "NoOfCalls": 1,
      "AvgNumberOfRows": 5,
      "MedianNumberOfRows": 5,
      "Inputs": [
        {
          "OperatorType": "Route",
          "Variant": "Scatter",
          "NoOfCalls": 1,
          "AvgNumberOfRows": 8,
          "MedianNumberOfRows": 8,
          "ShardsQueried": 2
        }
      ]
    }
  ]
}

This trace output shows the execution flow through different operators (Projection, Aggregate, Route) and provides statistics on the number of calls and rows processed at each step.

When to use TRACE #

Use vexplain trace when you need to thoroughly understand what a query is doing and where it's performing the most work. It's particularly useful for:

Identifying performance bottlenecks
Understanding query execution patterns
Optimizing complex queries
Debugging unexpected query behavior

By analyzing the trace output, you can gain insights into how your query is executed across different shards and operators, helping you make informed decisions about query optimization and database design.

`KEYS` Type #

The KEYS format provides a concise summary of the query structure, highlighting columns used in joins, filters, and grouping operations. This information is crucial for making informed decisions about sharding keys and optimizing query performance.

How it works #

VEXPLAIN KEYS analyzes the query structure without executing it. It identifies important columns that are potential candidates for sharding keys, including SARGable columns in the WHERE clause, join conditions, and grouping columns.

How to read the output #

The output is a JSON object containing the following key information:

groupingColumns: Columns used in GROUP BY clauses
joinColumns: Columns used in join conditions, not matter if they are on the WHERE or the JOIN ... ON clause
filterColumns: Columns used in WHERE clauses that are potential candidates for indexes (or vindexes), primary keys, or sharding keys. These typically include columns used in equality comparisons or range conditions.
statementType: The type of SQL statement (e.g., SELECT, INSERT, UPDATE, DELETE)

Example: #

mysql> vexplain keys select u.foo, ue.bar, count(*) from user u join user_extra ue on u.id = ue.user_id where u.name = 'John Doe' group by 1, 2;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ColumnUsage                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
        "groupingColumns": [
                "user.foo",
                "user_extra.bar"
        ],
        "joinColumns": [
                "user.id",
                "user_extra.user_id"
        ],
        "filterColumns": [
                "user.name"
        ],
        "statementType": "SELECT"
} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

When to use KEYS #

Use vexplain keys when you need to:

Identify potential sharding key candidates
Optimize query performance by understanding which columns are frequently used in filters and joins
Analyze query patterns across your application to inform database design decisions
Quickly understand the structure of complex queries

By analyzing the KEYS output across multiple queries, you can make more informed decisions about sharding strategies, potentially improving query performance and data distribution in your Vitess deployment.

Safety for DML #

The normal behaviour for VEXPLAIN is to not actually run the query for DMLs — it usually only plans the query and presents the produced plan for the PLAN type. Since vexplain ALL|QUERIES really runs your queries, you need to add a query directive to show that you are aware that your DML will actually run.

Example: #

mysql> vexplain queries insert into customer(email) values('abc@xyz.com');
ERROR 1105 (HY000): VT09008: vexplain queries/all will actually run queries

This is the error you will get is you do not add the comment directive to your VEXPLAIN statement.

Example: #

mysql> vexplain /*vt+ EXECUTE_DML_QUERIES */ queries insert into customer(email) values('abc@xyz.com');
+------+----------+-------+-----------------------------------------------------------------------+
| #    | keyspace | shard | query                                                                 |
+------+----------+-------+-----------------------------------------------------------------------+
|    0 | customer | 80-   | insert into customer(email, customer_id) values ('abc@xyz.com', 1001) |
+------+----------+-------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

Here we can see how vtgate will insert rows to the main table, but also to the two lookup vindexes declared for this table.

Note - MySQL client by default strips out the comments from the queries before it sends to the server. So you'll need to run the client with -c flag to allow passing in comments.

Analyzing a SQL statement using VTEXPLAIN >>

Introduction #

QUERIES Type #

How it works #

How to read the output #

Example 1: #

Example 2: #

PLAN Type #

How to read the output #

Example: #

ALL Type #

How to read the output #

Example: #

MYSQLPLAN Type #

How it works #

How to read the output #

Example: #

Supported and unsupported queries #

When to use MYSQLPLAN #

TRACE Type #

How it works #

How to read the output #

Example: #

When to use TRACE #

KEYS Type #

How it works #

How to read the output #

Example: #

When to use KEYS #

Safety for DML #

Example: #

Example: #

`QUERIES` Type #

`PLAN` Type #

`ALL` Type #

`MYSQLPLAN` Type #

`TRACE` Type #

`KEYS` Type #