beer planet

• Baron Schwartz presents
• only works for SELECTs
• nobody dares admit if they've never seen EXPLAIN
• MySQL actually executes the query
• at each JOIN, instead of executing the query, it fills the EXPLAIN result set
• everything is a JOIN (even SELECT 1)
• Columns in EXPLAIN
• id: which SELECT the row belongs to
• select_type
• simple
• subquery
• derived
• union
• union result
• table: the table accessed or its alias
• type:
• join
• range
• …
• possible_keys: which indexes looked useful to the optimizer
• key: which index(es) the optimizer chose
• key_len: the number of bytes of the index MySQL will use
• ref: which columns/constants from preceding tables are used for lookups in the index named in the key column
• rows: estimated number of rows to read
• extra
• using index: covering index
• using where: server post-filters rows from storage engine
• using temporary: an implicit temp table (for sorting or grouping rows, DISTINCT). No indication of whether the temp table is in memory or on disk
• using filesort: external sort to order result. No indication of which algorithm MySQL will use
• shows an insane EXPLAIN output with 8 EXPLAIN rows
• maatkit includes a tool called mk-visual-explain, which can construct a formatted tree
• Baron shows a demo and answers questions
• EXPLAIN EXTENDED followed by SHOW WARNINGS will give more output about how a query is executed

Artem Russakovskii is a San Francisco programmer, blogger, and future millionaire (that last part is in the works). Follow Artem on Twitter (@ArtemR) or subscribe to the RSS feed.

In the meantime, if you found this article useful, feel free to buy me a cup of coffee below.