User Comments

I think you should say that if the query returns no data, the explain won't work, and will say 'Impossible WHERE noticed after reading const tables'.

EXPLAIN appears to be empirical.

EXPLAIN does not look at
an index and a select and determine the potential use of
the index. Instead EXPLAIN appears to look at the actual
data and determine the actual use of the index. In a development
database this becomes problematic requiring the generations
of representative data. Occasionally it is difficult to predict
how the data will effect the application of an index and it
is not clear that your index does not support your select or
your test data does not exercise the index.

More details of what to expect from EXPLAIN should be included
in the document.

In a 5,500 rows tableset - the following query took 202.30 sec in MySQL Control Center prior to a "analyze table partners; analyze table phones;" query!!

select s.id as id, s.label_text, p.phone_number as phonenumber, f.phone_number as faxnumber from partners as s
left join phones as p on s.aid=p.aid and p.phone_type=0 and p.preferred=1
left join phones as f on s.aid=f.aid and f.phone_type=1 and f.preferred=1
where s.partner_type<3 and s.voided_by=0

So - my 'tip' is to really RTFM very carefully where it says: run analyze table frequently :)

(needless to say that after the analyze query - mySQL provided me with the initial 1,000 rows in just under 500 msecs!!)

When creating query's on large ammounts of data it's smart to take a look at the optimization part of the documentation.
It will help you understand the way MySQL executes query's.

Another good thing to read is this http://dev.mysql.com/doc/mysql/en/Data_size.html

The section contains information regarding keys and such which will also help you to improve SELECT perfomance (in regard to the explain method).

I have to add though that explain might give you a lot of usefull info, (IMHO) it lacks output relevant to single table queries. IE when making a FULLTEXT search on one table useing a multi column FULLTEXT index I get a keylen of 0. I'm not sure if this is the same as NULL (none used). A more precise explanation of what actually is going on would be nice.

To follow up on Andres's post, it should be noted that MySQL 4.0 and 4.1 differ in their use of a ResultSet with EXPLAIN.

In 4.0, you can receive a result with just a mysterious "Comment" column (none of the rest of the columns as described in explain.html) showing why it is angry at you.

In 4.1, you will receive the result set as described and the "Extra" column will contain the error message.

As an example, try the following query on a 4.0 server then on 4.1:

explain select * from mysql.db where 1=0\G

The key_len value is, I belive, given in bytes. So for a multiple-part key you have to know the length in bytes of the columns that make up the key.

For example, consider a key composed of (tinyint_col, integer_col, char5_col). The lengths of the individual columns are 1, 4 and 5 bytes respectively. So if MySQL only uses 1 part of the key you will see a value of 1 for key_len. If the first two parts of the key are used key_len will be 5, or 10 if all the key's parts are used.

(IMO the actual manual entry for key_len needs more explanation and emphasis, because for a multiple-column key how much of the key is used can have a huge effect on query speed. It would have saved me a lot of time if the manual had explained *how* to work out how much of a multiple-column key is used, instead of me having to work it out by trial and error!)

Add your own comment.