Working with the EXPLAIN statement
The EXPLAIN statement is the main tool to understand how a statement is executed within a server. In MariaDB 10, this works not only with the SELECT statements, but also with the UPDATE and DELETE statements. The syntax of the EXPLAIN statement is:
EXPLAIN [EXTENDED] <statement>;
The EXTENDED clause adds a column to the output and generates a note (which can be seen with the SHOW WARNINGS command) containing statement as it has been internally rewritten by the optimizer.
After MariaDB 10, another property of this command was added, shown as follows:
SHOW EXPLAIN FOR <thread_id>;
This command allows us to obtain the execution plan from a running statement. This is useful when a statement is taking a lot of time and we want to know the reason. To see the running queries and related thread IDs, SHOW PROCESSLIST can be used.
For example, to check how a query is executed and then see how it is rewritten, we can run the following code:
MariaDB [test]>...
