Java, MySQL, Avaya


The EXPLAIN statement is a valuable tool in terms of tuning your database, but before you get too excited, it is important to pay attention to all it can offer you and its limitations:

  • Shows MySQL’s estimated query plan.
  • Only works for SELECT queries.
The EXPLAIN output columns are:

field : (possible values)  description
id : (number) which SELECT the row belongs to.
select type : (SIMPLE or COMPLEX : primary, subquery, derived, union, union result)
  • SIMPLE, No subqueries or unions.
  • PRIMARY, The outer query
  • SUBQUERY, Numbered according to the position in SQL text
  • DERIVED, Subquery in the FROM clause executed as a temp table
  • UNION, Rows are spooled into a table, then read out with a NULL id in a row that says UNION RESULT

table : (table name) the table accessed or its alias.
type : (ALL, index, range, ref, eq_ref, const, system, NULL) ordered from worst to best by the ‘join type’. How MySQL will access the rows to find results.
possible_keys : (table columns) which indexes looked useful to the optimizer, the indexes that can help make row lookups efficient.
key : (table columns) which indexes the optimizer chose, but that does NOT mean they will be the most efficient indexes, different criteria.
key_len : (number) the number of bytes of the index MySQL will use
ref : (const, column name) which columns/constants from preceding tables are used for lookups in the index named in the key column
rows : (number) estimated number of rows to read, NOT the number of rows in the result set!
extra : (text) Any additional information that help us to understand the execution plan used.
  • Using index: covering index
  • Using where: server post-filters rows from storage engine
  • Using temporary: an implicit temporary table (for sorting or grouping rows, DISTINCT)
  • Using filesort: external sort to order results

August 20, 2011 - Posted by | MySQL

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: