EXPLAIN UPDATE/DELETE/INSERT in MySQL and MariaDB

MySQL 5.6 added support for EXPLAIN INSERT/UPDATE/DELETE. MySQL 5.7 made some improvements to EXPLAIN code. Also, 5.7 added support for EXPLAIN FOR CONNECTION, which looks very similar to MariaDB’s SHOW EXPLAIN. I was working on putting EXPLAIN INSERT/UPDATE/DELETE into MariaDB 10.0, so I’ve dug up some details and thought I’d share them:

EXPLAIN UPDATE/DELETE

EXPLAIN UPDATE looks like regular EXPLAIN SELECT:

MySQL [dbt3sf1]> explain update orders set o_orderpriority='1-URGENT' where o_orderDATE='1998-07-21';
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | const |  628 | Using where |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+

EXPLAIN PARTITIONS shows partitions. EXPLAIN EXTENDED shows the “filtered” column, but doesn’t produce a warning with query after rewrites.

In MySQL 5.7, select_type changes from SIMPLE to UPDATE.

MySQL [dbt3sf1]> explain update orders set o_orderpriority='3-MEDIUM' where o_orderDATE='1998-07-21';
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
|  1 | UPDATE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | const |  628 | Using where |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+

for multi-table UPDATEs (or DELETEs), select_type=UPDATE (or DELETE) only for tables that are being updated.

Inside, there are some issues. Best-release-ever still suffers from an old MySQL design where execution path taken by EXPLAIN is different from execution path taken by the actual query. If you have something in two places, it goes out of sync in no time, and I have alreay found one example EXPLAIN will show “Using join buffer” where the execution doesn’t use it (filed as BUG#70553). A shameless plug: in MariaDB 10.0, we use one execution path, so bugs like this one are not possible.

Another peculiarity is Using temporary. You can run a query like this:

MySQL [dbt3sf1]> explain update orders set o_orderDATE='1998-07-22' where o_orderDATE='1998-07-21';
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+------------------------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref   | rows | Extra                        |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+------------------------------+
|  1 | UPDATE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | const |  628 | Using where; Using temporary |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+------------------------------+

and see Using temporary. This looks weird, why would this query need a temporary table? Running the UPDATE and checking Created_tmp_tables shows that indeed no temporary tables were created. The reason for “Using temporary” is that the UPDATE modifies columns in the index that is used to find rows. When that happens, the optimizer needs to collect record ids in a buffer before doing any updates. But, records are collected in a buffer, not in a temporary table. We in MariaDB decided to denote this with Using buffer instead:

MariaDB [dbt3sf1]> explain update orders set o_orderDATE='1998-07-22' where o_orderDATE='1998-07-21';
+------+-------------+--------+-------+---------------+---------------+---------+------+------+---------------------------+
| id   | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows | Extra                     |
+------+-------------+--------+-------+---------------+---------------+---------+------+------+---------------------------+
|    1 | SIMPLE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | NULL |  628 | Using where; Using buffer |
+------+-------------+--------+-------+---------------+---------------+---------+------+------+---------------------------+

EXPLAIN INSERT

This one is weird - INSERT doesn’t have a query plan, why would one need EXPLAIN for it? One could argue that EXPLAIN INSERT ... SELECT makes sense, as the SELECT part has a query plan. However, using that logic, EXPLAIN CREATE TABLE … SELECT should have been supported also, but it isn’t.

In MySQL 5.6, EXPLAIN INSERT seems to be totally useless:

MySQL [dbt3sf1]> explain insert into orders (o_custkey) values ('32423');
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

In MySQL 5.7, it shows the table that we insert into:

MySQL [dbt3sf1]> explain insert into orders (o_custkey) values ('32423');
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | INSERT      | orders | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+

“type=ALL” is misleading, because no full table scan will take place. The only scenario when EXPLAIN INSERT can be useful is when one is doing an INSERT into partitioned table. In that case, EXPLAIN PARTITIONS will show the result of partition pruning. Note that partition pruning for INSERTs works in a rather peculiar way - depending on the number of rows inserted, it may choose not to perform partition pruning and will show that all partitions will be used.

I think this post is getting long, so I will talk about SHOW EXPLAIN and EXPLAIN FOR CONNECTION in a separate post.

Posted in Uncategorized on October 17th, 2013 by spetrunia | |

5 Responses to ' EXPLAIN UPDATE/DELETE/INSERT in MySQL and MariaDB '

Subscribe to comments with RSS or TrackBack to ' EXPLAIN UPDATE/DELETE/INSERT in MySQL and MariaDB '.

  1. Federico Razzoli said,

    on October 18th, 2013 at 5:07 pm

    I have a question: “In MariaDB 10.0, we use one execution path, so bugs like this one are not possible” - does this mean that using SHOW EXPLAIN will be unnecessary with 10.0?

  2. dveeden said,

    on October 19th, 2013 at 1:39 pm

    I would have expected to see lookups on unique keys in the ‘explain insert’ output. And a difference between insert on a table with and without a primary key would have been helpful. Even the optimizer trace doesn’t really show any difference.

  3. dveeden said,

    on October 19th, 2013 at 2:10 pm

    As a comparison: PostgreSQL 9.1 also doesn’t show that much info on a explain insert.

    dveeden=# create table t1(id serial, name varchar(200));
    NOTICE: CREATE TABLE will create implicit sequence “t1_id_seq” for serial column “t1.id”
    CREATE TABLE
    dveeden=# explain insert into t1 values(1,’test’);
    QUERY PLAN
    ————————————————
    Insert on t1 (cost=0.00..0.01 rows=1 width=0)
    -> Result (cost=0.00..0.01 rows=1 width=0)
    (2 rows)

  4. spetrunia said,

    on October 20th, 2013 at 7:03 pm

    Hi Frederico,

    I think, SHOW EXPLAIN will be still useful. If there is a query that’s already running, it is useful to see what query plan this query is using (so that you know whether it makes sense to wait for the query to end, or it is hopeless. It would be nice if SHOW EXPLAIN could show how far the query has progressed, but we’re not there yet).

  5. spetrunia said,

    on October 21st, 2013 at 12:03 pm

    @dveeden: yes, I agree that printing out Unique index or FK checks would have helped.

    However, this is difficult to implement in MySQL (and MariaDB) architecture. Checks for FKs or Unique key constraints are done inside the storage engine. The optimizer has no idea about how the checks are made.

    (A while ago I was working on “table elimination” feature that could have worked better if it could access FK definitions.. No luck - FKs are hidden somewhere deep inside InnoDB, there is no easy way to access them)

Leave a reply

You must be logged in to post a comment.