index not used when join condition added

[Daniel Walter]

According to EXPLAIN, when I use my query without a join, the table is read using the index on the datetime column with access type range.

SELECT COUNT(*) FROM eodxmlperiods WHERE eodxmlperiods.datetime BETWEEN '2011-12-12 01:00:00' AND '2011-12-12 23:00:00'

When I join this query to another table, eodxmlperiods is still read first according to EXPLAIN, but it is now read using the ALL access type rather than using the index in a range fashion. This is a join to a primary key of the secondary table so the secondary table has an index access type eq_ref.

SELECT COUNT(*) FROM eodxmlperiods, idfromsymbol WHERE eodxmlperiods.datetime BETWEEN '2011-12-12 01:00:00' AND '2011-12-12 23:00:00' AND eodxmlperiods.symbolid=idfromsymbol.id AND idfromsymbol.exchange=AMEX


Even when I add USE INDEX for the datetime column, it still does not use the index. Adding FORCE INDEX does work though. I am surprised that this step is necessary. Am I doing something wrong here? It really doesn't seem like any index hint should be necessary for this query.