1

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 and blogger. 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.

Tags: , ,