≡ Menu

How to verify whether MySQL is using the index I created?

The question is – how to verify whether MySQL is actually using the index you created on a table in the SQL query?

There is a very simple method to do this. Just use the EXPLAIN keyword before the SQL query.

By using EXPLAIN before a MySQL query in phpMyAdmin, you see several fields that MySQL used when the query was run. It shows the key that was used as an index and it also shows you other possible keys that it could have used. It shows you the type of the query (simple, join etc).

So to verify whether MySQL used the index you created, just verify that the key column in the execution plan put forward by EXPLAIN contains the index as the key that MySQL used for the query.

How to understand the output of the MySQL EXPLAIN statement?

See the following image in which I used the query:

EXPLAIN SELECT * FROM alexadescription WHERE domain = ”google.com”

The query simply does a SELECT from a table called alexadescription for the domain name google.com. There is an index on the column domain.

See the following image which shows the EXPLAIN command output:

Understanding MySQL EXPLAIN

I will go over the details on what each of these fields mean:

id - This is the identifier of the SELECT statement. Since there is only one statement, this field is not useful.
select_type - SIMPLE means that the SELECT statement is not a UNION of two or more SELECT statements
table - The table name, in this case alexadescription
type - The join type used for the query
possible_keys - A list of all possible keys that MySQL could use
key - The key that was chosen to be used for the query by MySQL
key_len - The length of the key
ref - The field that was compared to the index. In this case, it was a constant
rows - The number of rows retrieved

So as you see, the key is the column name domain which is as expected. This allows you to see the query execution plan for simple queries like this SELECT query, but also more complicated joins which could involve more than one table.

So to verify whether MySQL is using your index in the SQL statement, just examine the key field of the output of the EXPLAIN statement.

{ 0 comments… add one }

Leave a Comment