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.<\/p>\n
EXPLAIN only works for SELECT queries.<\/p>\n
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.<\/p>\n
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…).<\/p>\n
key_len – to know if your table is indexed well.<\/p>\n
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.<\/p>\n
Maatkit includes mk-visual, so you can have a visual explain. This is also, very machine readable.<\/p>\n
Update:<\/strong> Artem has good notes<\/a> too.<\/p>\n Technorati Tags: mysqluc08<\/a>, mysqluc2008<\/a>, mysql<\/a>, baron schwartz<\/a>, explain<\/a><\/p>\nShare this:<\/h3>