Subscribe to this rss feed RSS

Best way to visualize EXPLAIN?

Posted November 5th, 2008 at 11:37 by Bjorn, Jet Profiler team
2 Comments
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?

2 comments

Add your own

dede | March 16th, 2009 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 * ...

Thanks
Bjorn | March 25th, 2009 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


Name (required)

Email (will not be published) (required)

Website



Notify me of followup comments via e-mail



Subscribe without commenting


Your email: