einpoklum einpoklum - 3 months ago 18
MySQL Question

How can I EXPLAIN several consecutive queries without executing them?

Suppose I have a pair of arbitrary SQL queries, each one depending upon the former ones, e.g.

CREATE VIEW v1 ( c3 ) AS SELECT c1 + c2 FROM t1;
SELECT sum(c3) FROM v1;
DROP VIEW v1;


(but note I am not asking about these specific queries - this is just an example; assume I get the queries from a file and do not know them in advance.)

Now, I want to get my DBMS to EXPLAIN its plan for all of my queries (or an arbitrary query in the middle, it's the same problem essentially) - but I do not want it to actually execute any of them.

Is this possible with (1) MySQL? (2) PostgreSQL? (3) MonetDB?

Answer

PostgreSQL

You may use the explain statements as follows.

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Refer this, documentation answers your question.

MonetDB

Same as above use BEGIN ROLLBACK statements.

Refer this.

MySQL

MySQL explain it self does what you need. No need to ROLLBACK.

Refer this answer.

Comments