Query not using full index

[Perry Stupp]

I am working on a performance problem and would appreciate any insight that you can provide on this matter.

I am working with two queries that are essentially "equivalent" in that they return the same resultset. While the "data" is retrieved from only one table, the difference between the two queries is that one explicitly references several foreign key id's while the other query needs to look them up; i.e.

Query 1 -

select * from datatable where fk1 = 1 and fk2 = 2 and datetime between 'DATE1' and 'DATE2'...

vs.

Query 2 -

select *
from datatable, fktable1, fktable2
where fktable1.name = 'one' and fktable2.name = 'two'
and datatable.fk1 = fktable1.id and datatable.fk2 = fktable2.id
and datetime between 'DATE1' and 'DATE2'

The index needed to optimize the query is

fk1, fk2, datetime

Now, when I run the explain plan on the first query it indicates that it needs to search only 159 records which is pretty much as I expected / want. i.e.

SIMPLE | datatable | range | index1, index2 | index1 | 26 | NULL | 159 | Using where

when I run explain plan on the second query, the number of rows that need to be searched for the same data is 14736.

S| fktable1 | C | fkindex1 | fkindex1|100 | C | 1 |
S| fktable2 | C | fkindex2 | fkindex2| 50 | C | 1 |
S| datatable | ref | index1, index2 | index1 | 22 |C, C| 14736 | Using where

where S = SIMPLE, C = const

The difference is that the key_len of the second query is shorter - presumably because it is not using the date portion of the index (and hence the extra rows to search). I guess my question is, why isn't it using the full index on the second query? Is this working as expected or possibly a bug? I'm running v4.1.7.

Thanks in advance for your assistance.
Regards,

Perry.