Fixed: EXPLAIN UPDATE Says "not executable by iterator executor" in MySQL

EXPLAIN UPDATE gave me one line: EXPLAIN: -> <not executable by iterator executor>. It isn't an error, and it isn't useful either. In MySQL 9.7, the default output format can't describe this kind of statement, and the older format can.

What I Was Doing

I wanted to know whether an UPDATE uses an index, on MySQL 9.7.4 in Docker:

mysql

EXPLAIN UPDATE products SET ReorderLevel = 6 WHERE Name = 'HDMI cable, 2 m'\G

What I Expected

A plan, with the index it would use and the number of rows.

What I Got

Output

*************************** 1. row ***************************
EXPLAIN: -> <not executable by iterator executor>

1 row in set (0.000 sec)

What I Checked

A SELECT with the same WHERE. That one's fine:

mysql

EXPLAIN SELECT Id FROM products WHERE Name = 'HDMI cable, 2 m'\G

Output

*************************** 1. row ***************************
EXPLAIN: -> Filter: (products.`Name` = 'HDMI cable, 2 m')  (cost=2.65 rows=2.4)
    -> Table scan on products  (cost=2.65 rows=24)

1 row in set (0.000 sec)

The format in use.

mysql

SELECT @@explain_format;

Output

+------------------+
| @@explain_format |
+------------------+
| TREE             |
+------------------+
1 row in set (0.000 sec)

TREE. The system variables page lists it as the default value of explain_format.

The other formats. JSON says the same thing in more words:

mysql

EXPLAIN FORMAT=JSON UPDATE products SET ReorderLevel = 6 WHERE Name = 'HDMI cable, 2 m'\G

Output

*************************** 1. row ***************************
EXPLAIN: {
  "query_plan": {
    "operation": "<not executable by iterator executor>"
  },
  "query_type": "update",
  "json_schema_version": "2.0"
}
1 row in set (0.000 sec)

Why It Happens

The TREE format describes the plan of MySQL's iterator executor, and the message says that this statement doesn't run on it. The UPDATE itself is fine, and it's only the description that's missing.

The Fix

Name the older format in the statement:

mysql

EXPLAIN FORMAT=TRADITIONAL UPDATE products SET ReorderLevel = 6 WHERE Name = 'HDMI cable, 2 m'\G

The page says: "Using a format specifier with EXPLAIN or EXPLAIN ANALYZE overrides any setting for explain_format." Or change it for the whole session:

mysql

SET @@explain_format = TRADITIONAL;

After the Fix

Output

*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: products
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 24
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.000 sec)

possible_keys: NULL says there's no index for this WHERE, and rows: 24 says it reads the whole table. That has a price: Why a MySQL UPDATE Without an Index Locks Every Row.

Common Mistakes

Mistake	What happens	Do this instead
Reading this as an error in the UPDATE	It runs fine	Change the format
Trying FORMAT=JSON	The same message, as JSON	Use TRADITIONAL
Assuming a SELECT plan matches the UPDATE	They can differ	EXPLAIN the UPDATE itself

Troubleshooting

Symptom	Likely cause	Fix
The same message for a DELETE	The same limit	Use FORMAT=TRADITIONAL
1 warning after the plan	The rewritten statement is there	Run SHOW WARNINGS to read it
The format isn't the default on your server	Someone set explain_format	SELECT @@explain_format

Conclusion

<not executable by iterator executor> means the default TREE format can't describe this statement. Use EXPLAIN FORMAT=TRADITIONAL UPDATE ... to see the plan.

Additional Resources

• EXPLAIN Statement — the formats.
• explain_format — the variable.

Related

• How to Check if a MySQL Query Uses an Index with EXPLAIN
• Why a MySQL UPDATE Without an Index Locks Every Row
• How to Update Rows in a MySQL Table Safely