EXPLAIN Demystified
Baron Schwartz gave a most interesting talk about EXPLAIN. You will definitely want to read his slides (filled with detail), when they make their way online. These notes are very sparse, just bits that I didn’t see in the slides, that Baron mentioned verbally. Plenty of good questions, and plenty of interaction.
EXPLAIN only works for SELECT queries.
How does MySQL execute queries? Optimisation happens even as the query is being executed. As the query is being optimised, some execution happens as well. Execution Plan is a data structure, not bytecode.
When EXPLAIN’s output is generated, MySQL actually executes the query. It just set’s DESCRIBE on it, rather than executing it. Everything is a JOIN to MySQL (union, SELECT 1 [simplest base case join], etc…).
key_len – to know if your table is indexed well.
rows: estimated number of rows to read, but not the number of rows in the result set. In 5.1 and greater, it reflects LIMIT, but not before.
Maatkit includes mk-visual, so you can have a visual explain. This is also, very machine readable.
Update: Artem has good notes too.
Technorati Tags: mysqluc08, mysqluc2008, mysql, baron schwartz, explain
If only it worked properly with subqueries (it gives weird results for them in 5.0)