We've all had problems with slow queries, which can lead to sluggish applications and dissatisfied users. You've looked at your code and just can't figure out what's causing the problem. Have you looked at the effectiveness of your MySQL queries? Here's an easy way to analyze your queries to make sure they are being executed efficiently.

Here's a very simplistic example. Let's start out by creating a table of NFL teams.

So a sample record would look like this:

We're going to start out with a simple query. Let's try:

Our results:

Simple enough, right? Now the question is...is this query as efficient as it could be?

MySQL's EXPLAIN syntax shows how queries are being executed. Is the query correctly using indexes...is it utilizing indexes at all?

Let's try running EXPLAIN on the previous query:

The result of the EXPLAIN query is below:

So, what does this tell us?

It shows that there was one result returned using a simple SELECT query of the "nflteams" table. It also shows the following:

type: This shows what type of join is being used. Here's a quick an dirty explaination of some the possible "types" you will see from best possible result to worst (descriptions from MySQL manual):

• system: The table only has one row
• const: The table has at most one matching row, which is read at the start of the query.
• eq_ref: One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE index.
  
• ref: All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.
  
• range: Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.
  
• index: This join type is the same as ALL, except that only the index tree is scanned. This usually is faster than ALL because the index file usually is smaller than the data file.
  
• ALL: A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

As you can see, our query returned the least desirabe result (ALL). A full table scan was done and on large table this can be incredibly slow.

possible_key and key: Shows which indexes were available and which ones were actually used in the query. In our case it was NULL since we had no keys.

key_len: Shows the length of the key used. Again, ours was NULL since we had no keys. Note: the smaller number the better.

ref: Shows which columns or constants are compared to the index named in the key column to select rows from the table.

rows: The number of rows that were scanned. In our case it was 32..the total amount of records in the table (a full table scan)

Extra: Denotes additional information about the query. Note: stay away from value

Now that we know our query isn't fully optimized, let's try adding an index on "nflteam_id"

Now let's run the EXPLAIN statement again and see what we get.

This time around we did much better. The query returned 1 result using a simple SELECT. It was utilizing a PRIMARY key (nflteam_id, key length of 2). Since it used an index, MySQL knew where to look and only scanned 1 row (instead of the previous full table scan of 32).

Again, this is a very simple example, but as you can imagine EXPLAIN can come in quite handy when analyzing complex JOIN queries on large tables.

Bookmark to: