Why is the REF column NULL, and why is the key_len 5?

[Taras D]

Hi everyone,

Should be an easy question, but I couldn't find the answer anywhere.
Firstly, I can't figure out why the REF column shows NULL, and secondly I
don't know why the key length is 5.

I have the following db:

table l
(
 aId int unsigned not null auto_increment primary key,
 sId smallint unsigned not null,
 dId smallint unsigned,
 index(sId,dId)
);

table d
(
 sId smallint unsigned not null,
 dId smallint unsigned not null,
 primary key(sId, dId)
);

explain select straight_join * from l,d where l.sId = d.dId and d.sId = 1;

+----+-------------+-------+--------+---------------+---------+---------+----------------------------------------+------+-------------+

| id | select_type | table | type   | possible_keys | key     | key_len |
ref                                    | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------------------+------+-------------+

|  1 | SIMPLE      | l     | index  | sId           | sId     | 5       |
NULL                                   |    5 | Using index |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4       |
const,radius_searching_test_case.l.sId |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------------------+------+-------------+

So even though its using the index for table 'l', the ref is NULL. I think
it may be because mysql is using the index not to constrain the 'l' table in
any way (which I think is impossible seeing the only constraint the 'l'
table has has got to do with the 'd' table, and the 'd' table is joined to
the 'l' table), but simply to select the data for the output.

Secondly, why is the key_len 5? The MySQL manual is unclear about the exact
definition of the key_len. The last user comment on
http://dev.mysql.com/doc/refman/5.0/en/explain.html suggests that it is the
number of bytes that are used. However, sId is a smallint (2 bytes). The
'display width' of the smallint is set to 5, but I don't see why this would
appear in the key_len column.

To replicate:
---------------------------------------------------------------------------
Run the following:

DROP TABLE IF EXISTS `l`;
DROP TABLE IF EXISTS `d`;

create table l
(
 aId int unsigned not null auto_increment primary key,
 sId smallint unsigned not null,
 dId smallint unsigned,
 index(sId,dId)
);

create table d
(
 sId smallint unsigned not null,
 dId smallint unsigned not null,
 primary key(sId, dId)
);

insert into l values(NULL,1,1);
insert into l values(NULL,2,1);
insert into l values(NULL,3,2);
insert into l values(NULL,3,3);
insert into l values(NULL,2,1);

insert into d values(1,1);
insert into d values(1,2);
insert into d values(1,3);
insert into d values(2,1);
insert into d values(2,2);
insert into d values(2,3);
insert into d values(3,1);
insert into d values(3,2);
insert into d values(3,3);

explain select straight_join * from l,d where l.sId = d.dId and d.sId = 1;