Best way to visualize EXPLAIN?

Posted November 5th by Bjorn, Jet Profiler team
Interpreting the output of the MySQL EXPLAIN command can be tricky. From all the information you get, some of the most important information is:

  • Full table scans
  • High join size product
  • Using filesorts
  • Using temporary tables

This can be hard to see in the raw output. Example query:

EXPLAIN SELECT b.item_id, b.bidder_id, u.username, MAX(price) AS price FROM bid b JOIN user u ON (b.bidder_id = u.user_id) WHERE b.item_id = '1' GROUP BY b.bidder_id ORDER BY price DESC

The explain outputs:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, b, ALL, null, null, 0, null, 22660, Using where; Using temporary; Using filesort
1, SIMPLE, u, eq_ref, PRIMARY, PRIMARY, 4, heavybidder.b.bidder_id, 1,

We've been experimenting how to visualize the output of the EXPLAIN command. Using boxes for tables, calculating the join size product and highlighting important information. What do you think of the result so far?

Screenshot:Explain visualizer

Ideas, comments?


Add your own

dede | March 16th at 11:11
Sorry for my poor english, I am french

do you plan to autocreate the index on slow queries ?
and/or cut/add the original query into 2 Basic queries
like create temporary table ... select * ...

Bjorn | March 25th at 06:56
Hello dede, no worries about the english. We are working on analyzing the query and giving suggestions on how to add a covering index.

About your second suggestion, could you explain a little more?

Post a comment


(will not be published) (required)

Notify me of followup comments via e-mail

Subscribe without commenting