EXPLAIN syntax. Get information about a SELECT.

	EXPLAIN SELECT select_options

Gives information about how and in which order tables are joined. With the help of EXPLAIN one can see when one has to add more indexes to tables to get a faster select that uses indexes to find the records. You can also see if the optimiser joins the tables in an optimal order. One can force the optimiser to use a specific join order with the STRAIGHT_JOIN option to select.

The different join types are:

system

The table has only one record (= system table)

const

The table has at most one matching record which will be read at the start of the query. All columns in this table will be regarded as constants by the rest of the optimiser.

eq_ref

One record will be read from this table for each combination of the previous tables.

ref

All rows with matching indexes will be read from this table for each combination of the previous tables.

range

Only rows that is in a given index range will be retrieved trough an index. The extra column will tell which index is used.

all

A full table scan will be done for each combination of the previous tables.

Here is a example of a join which is optimised with the help of EXPLAIN.

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
        tt.ProjectReference, tt.EstimatedShipDate,
        tt.ActualShipDate, tt.ClientID,
        tt.ServiceCodes, tt.RepetitiveID,
        tt.CurrentProcess, tt.CurrentDPPerson,
        tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
        et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1,
        do
        WHERE tt.SubmitTime Is Null and tt.ActualPC =
        et.EMPLOYID and tt.AssignedPC =
        et_1.EMPLOYID and tt.ClientID =
        do.CUSTNMBR;

The EXPLAIN returns the following:

table   type    possible_keys   key     key_len ref     rows    Extra
et      ALL     PRIMARY NULL    NULL    NULL    74
do      ALL     PRIMARY NULL    NULL    NULL    2135
et_1    ALL     PRIMARY NULL    NULL    NULL    74
tt      ALL     AssignedPC,ClientID,ActualPC    NULL    NULL    NULL   3872
        range checked for each record (key map: 35)

In this case MySQL is doing a full join for all tables! This will take quite a long time as the product of the number of rows in each table must be examined! So if all tables had 1000 records MySQL has to look at 1000^4 = 1000000000000 rows. If the tables are bigger you can only imagine how long it would take...

In this case the first error is that MySQL can't yet use efficiently indexes on columns that are declared differently: (varchar() and char() are not different in this context)

In this case tt.ActualPC is char(10) and et.EMPLOYID is char(15).

Fix:

mysql> alter table tt change ActualPC ActualPC varchar(15);

And the above explanation shows:

table   type    possible_keys   key     key_len ref     rows    Extra
tt      ALL     AssignedPC,ClientID,ActualPC    NULL    NULL    NULL   3872
        where used
do      ALL     PRIMARY NULL    NULL    NULL    2135
        range checked for each record (key map: 1)
et_1    ALL     PRIMARY NULL    NULL    NULL    74
        range checked for each record (key map: 1)
et     eq_ref  PRIMARY PRIMARY 15      tt.ActualPC    1

Which is not perfect but much better. This version is executed in a couple of seconds.

After

mysql> alter table tt change AssignedPC AssignedPC varchar(15),
                      change ClientID Clientid     varchar(15);

You get the following from EXPLAIN:

table   type    possible_keys   key     key_len ref     rows    Extra
et      ALL     PRIMARY NULL    NULL    NULL    74
tt      ref     AssignedPC,ClientID,ActualPC    ActualPC        15
        et.EMPLOYID   52      where used
et_1    eq_ref  PRIMARY PRIMARY 15      tt.AssignedPC  1
do      eq_ref  PRIMARY PRIMARY 15      tt.Clientid    1

Which is 'almost' as good as it can get. The problem is that MySQL assumes that tt.AcutalPC is evenly distributed which isn't the case in the tt.

Fortunately it is easy to tell MySQL about this:

shell> isamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

And now the join is 'perfect':

table   type    possible_keys   key     key_len ref     rows    Extra
tt      ALL     AssignedPC,ClientID,ActualPC    NULL    NULL    NULL
        3872     where used
et      eq_ref  PRIMARY PRIMARY 15      tt.ActualPC    1
et_1    eq_ref  PRIMARY PRIMARY 15      tt.AssignedPC  1
do      eq_ref  PRIMARY PRIMARY 15      tt.Clientid    1

Go to the first, previous, next, last section, table of contents.