EXPLAIN - The Most Powerful MySQL Command

The most powerful command in MySQL is EXPLAIN. EXPLAIN can tell you exactly what happens when you execute a query. This information will allow you to detect slow requests and reduce the time spent processing the request, which can later significantly speed up your application.

How to use the EXPLAIN command

Here is a very simple example of using this command:



Database schema:



(table with users users)





(table with addresses address)



')

In this example, the user data is selected based on its userid.

Here is what we have as a result of the EXPLAIN query:

Variable	Value
	The identifier (ID) of the table in the query. EXPLAIN creates one entry for each table in the query.
	Possible values: SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBSELECT, and DERIVED.
	The name of the table from which MySQL reads data
	The type of join that MySQL uses. Possible values: eq_ref, ref, range, index, or all.
	A list of indexes (or NULL if there are no indexes) that MySQL can use to sample rows in a table.
	The name of the index that MySQL uses (after checking all possible indexes).
	Key size in bytes.
	Columns or values ​​that are used to compare with the key.
	The number of rows that MySQL needs to check to process a request.
	Additional information about the request.

This example is quite simple. We perform a search on the primary key (userid) and there can be only one entry that fits our conditions (the rows variable is 1).



Here is a more advanced example:



This query is more advanced than the first. It combines the users and address tables based on userid. The userid field is the primary key of the users table, but it is not an index in the address table. The result of the EXPLAIN command in this case will be the following:

(table users)

Type: const

Possible_Keys: primary

Ref: const

(address table)

Type: all

Possible_Keys: (nothing)

Ref: (nothing)

The first table is optimized. A primary key is used to fulfill the request. The second table is not optimized. The value of the type parameter is all, and Possible_keys is empty, which means that a full table scan will be performed. Adding an index to the user field of the second table will make it optimized.

The output of the EXPLAIN command after optimizing the second table will be as follows:

(table users)

Type: const

Possible_Keys: primary

Ref: const

(address table)

Type: const

Possible_Keys: primary

Ref: const

Additional information about the EXPLAIN command can be found in the official MySQL documentation: dev.mysql.com/doc/refman/5.0/en/explain.html