I believe I’m nearly done with the SHOW EXPLAIN feature. The idea is that if you’ve got some long-running query $LONG_QUERY running in connection $LONG_QUERY_CONN, you should be able to create another connection, run SHOW EXPLAIN FOR $LONG_QUERY_CONN and see what query plan is being used to run the $LONG_QUERY.

How is this different from just running explain $LONG_QUERY? First, you don’t need to replicate the exact environment that $LONG_QUERY was run in: you don’t need to figure out what values it had for @@optimizer_switch and other settings, what were the contents of its temporary tables, if any, what did InnoDB told the optimizer about the table statistics, etc.

Another, indirect benefit is that we will now be able to produce query’s EXPLAIN at arbitrary point in time, which should make it possible to store it in the slow query log, or support EXPLAIN ANALYZE command.

To the uninformed, coding SHOW EXPLAIN feature may seem trivial: one will only need to walk the query plan and print it out. In reality, it is not as easy: first, you need to take into account that MySQL/MariaDB has a habit of optimizing parts of query plan lazily, also, it is eager to free parts of query plan that are no longer needed. Then, there are slight differences between data structures used by EXPLAINs and regular SELECTs.

It also turns out EXPLAIN had some skeletons in its closet. We have been testing SHOW EXPLAIN by comparing its output with EXPLAIN output, and I have already found two cases where EXPLAIN’s output doesn’t match the query execution. Which is scary, because I personally used to trust EXPLAIN, and everybody I worked with trusted it, too.

I’ve fixed (in MariaDB) one of the wrong EXPLAINs, MDEV-410, since it relates to subqueries and we have a lot of expertise in that code. The second one, MDEV-326, remains unfixed, because it’s in the sorting/grouping code and I’m afraid I can open a big can of worms if I touch it.