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…

    Read the rest of this article »