EXPLAIN Demystified

Architecture and Technology, General, Performance Tuning and Benchmarks
Location: Ballroom D Level: Intermediate
Presentation: EXPLAIN Demystified Presentation [PDF]
Average rating: ****.
(4.67, 3 ratings)

EXPLAIN was created by and for the MySQL developers, and then offered to the rest of the world with little information on how to interpret it. Simple queries are easy to understand, but queries with filesorts, subqueries, and UNIONs become dizzyingly complicated to unravel. There are a lot of undocumented rules about how EXPLAIN shows its output, too.

I learned EXPLAIN by studying source code, documentation, and learning from the query optimizer team. I verified everything I learned with complex test cases. Along the way I discovered how to reverse-engineer EXPLAIN into a query execution plan. Finally, I wrote a program to do this, the first of its kind.

In this session I’ll teach EXPLAIN from the ground up:

  • How MySQL transforms a query into an execution plan
  • How MySQL really executes queries (few people know this!)
  • How the execution plan maps onto EXPLAIN
  • How to work backwards from EXPLAIN to a query execution plan
  • Undocumented rules about subqueries, filesorts, and more
  • What the ID, select_type, and type columns really mean

I will (briefly!) demonstrate the visual explain tool I wrote, but I’ll focus on understanding EXPLAIN without tools. This session will give you a deeper understanding of what your queries are really doing.

Photo of Baron Schwartz

Baron Schwartz


Baron Schwartz is the founder and CTO of VividCortex, the best way to see what your production database servers are doing. Baron has written a lot of open source software and several books, including High Performance MySQL. He’s focused his career on learning and teaching about performance and observability of systems generally, including the view that teams are systems and culture influences their performance, and databases specifically.

  • Kickfire
  • Zmanda, Inc.
  • Continuent
  • EDS
  • JasperSoft
  • Sun Microsystems
  • Symantec Anti-Virus Software
  • XAware
  • Data Direct Technologies
  • Dolphin Interconnect Solutions
  • Hewlett Packard
  • Infobright, Inc
  • Linagora
  • Microsoft
  • OpSource
  • Oracle
  • Pentaho
  • R1Soft
  • Red Hat
  • Ticketmaster
  • TechRepublic

Contact Us

View a complete list of MySQL contacts.