MySQL Explain Plan
I finally got the magic trick to find the explain plan for a query in MySQL, but chagrined to find out that you can’t explain the cost or execution of INSERT, UPDATE or DELETE statements. This blog shows you how to get the execution and execution plan for a query and the error when you try to EXPLAIN DML (Data Manipulation Language) statements.
You can see the selection type, possible indexes, chosen index, et cetera:
EXPLAIN query; |
You can see the selection type, possible indexes, chosen index, et cetera plus the query execution plan with the magic EXTENDED key word:
EXPLAIN EXTENDED query; |
In some cases, you may need to type SHOW ERRORS to see the execution plan. There’s no way to format it natively, like Oracle, but there may be a tool out there.
If you try to explain a DML statement, you’ll get an error message like the following:
ERROR 1064 (42000): You have an error IN your SQL syntax; CHECK the manual that corresponds TO your MySQL server version FOR the RIGHT syntax TO USE near 'UPDATE city SET District = 'Funny'' at line 1 |
The last rule of thumb on query execution is that you should avoid subqueries because they degrade processing speed.
Subscribe via RSS