The most powerful mysql command

The most powerful command within mysql is explain. Explain can tell you exactly what mysql is doing when you execute a query and with this information, you can find slow queries and minimize execution time, which can significantly improve the speed of your web application.

How to use the explain command

Here is a simple example of its usage:

Database Schema:

(users table)

(address table)

In this simple example, I am selecting a record that is representing a user, based on a userid.

Here is the output:

Variable	Details
	The ID of this table in the query. EXPLAIN will create one output record for each table in the query.
	possible values: SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBSELECT, and DERIVED.
	The name of the table MYSQL will read the records from.
	The type of join that mysql will use. Possible values: eq_ref,ref,range, index, or all.
	a list of indexes (or NULL if none) mysql can use to find rows in the table.
	The name of the index MYSQL will use (after checking all possible indexes).
	The size of the key in bytes.
	The columns or values that are used to match against the key
	The number of rows mysql thinks it needs to examine to execute the query.
	extra information about the query

This example is pretty straight forward. Since we are peforming a search based on the primary key (userid), there can be only one record that can possible match (the rows variable is 1).

A more advanced example:

This query is more advanced than the first one. It is performing an inner join on the users and address table based on the userid. The userid is a primary key within the users table, but it is not an index in the address table. The output of the explain command shows us the following:

(users table)

Type: const
Possible_Keys: primary
Ref: const

(address table)

Type: all
Possible_Keys: (none)
Ref: (none)

The first table is optimized. It is using the primary key for this query. The second table, however is not optimized. The type is all and the Possible_keys=(none), which means it is going to have to go through a full table-scan. Adding an index on the user field optimizes this query.

Final output after index is added:

(users table)

Type: const
Possible_Keys: primary
Ref: const

(address table)

Type: const
Possible_Keys: primary
Ref: const

More information on this command can be found Here