2

I read about MySQL EXPLAIN safety in the StackOverflow question about it, and one of the later commenters (@tye) said:

people figuring out how to make updates via "explain"

It's a question that I've been thinking about today -- is there any way to actually change/modify/alter data in the database server with an EXPLAIN EXTENDED ... statement?

EXPLAIN ANALYZE ... in MYSQL 8.x+ seems entirely capable of changing data due to the fact that it executes the query and therefore could have impact if the statement changes anything. That makes sense... but my question is around a raw EXPLAIN EXTENDED ... statement.

Is there any way to change data with one?

I've executed a lot of different EXPLAIN ... statements, and I've never seen one that changed data... but I'm curious if someone cleverer then me knows of a way to achieve it. The way that I'm thinking about it, I can't think of any way to actually change data with an EXPLAIN EXTENDED ... statement, because:

  • The outermost query is never executed, it is only analyzed/planned. So it cannot have any impact on the data within the database server.
  • Subqueries may be executed and can therefore possibly cause performance problems, but subqueries in MySQL are always SELECT statements... which should be safe?
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Remi
  • 33
  • 4

3 Answers3

0

EXPLAIN and EXPLAIN EXTENDED don't run the statement. Ever. They just generate the query plan and return it in the result set and, with EXTENDED, in the warnings.

EXPLAIN ANALYZE (just ANALYZE in MariaDb) always runs the statement and gives back actual performance data.

You're safe EXPLAINing your statements, unless there's some unbounded temp table mess like Bill mentioned in it.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

To estimate table statistics, EXPLAIN has to actually run the subquery in queries similar to the below format.

EXPLAIN SELECT ... FROM (SELECT ... ) AS t;

This still doesn't change data in the original tables, but it could in theory create a temp table with unbounded size, and that could impact concurrent queries if it overtaxes I/O or fills up available disk space.

Subqueries cannot be any statement that modifies data. Subqueries can only be SELECT, TABLE, or VALUES — all these are read-only statements.

Be aware that if you EXPLAIN a DML statement such as INSERT, UPDATE, DELETE, REPLACE, you need privileges to run those queries, even though the EXPLAIN won't actually perform those operations to modify data.

Also you can't run EXPLAIN for those DML statements on an instance with read_only=1 set. That still does not mean that the EXPLAIN will modify data.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

When attempting EXPLAIN ANALYZE UPDATE ... on Version 8.0.32, I get

<not executable by iterator executor>
Rick James
  • 135,179
  • 13
  • 127
  • 222