MySQL Conference Liveblogging: EXPLAIN Demystified (Tuesday 2:00PM)
Posted by Artem Russakovskii on April 15th, 2008 in Databases
- 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.
beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who currently works at 
Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.
[...] Artem has good notes [...]