MySQL Forums
Forum List  »  Optimizer & Parser

index not used when join condition added
Posted by: Daniel Walter
Date: January 02, 2012 11:55AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
index not used when join condition added
2350
January 02, 2012 11:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.