MySQL Forums
Forum List  »  Newbie

Index on datetime type
Posted by: Manzell Blakeley
Date: November 17, 2010 09:57AM

I have a large table (~3 million rows) which contain a datetime values (actually two - a "datetime_start" and "datetime_end". let's call it table1.

In my query, I generate from another table a list of specific datetime values (call it table2), and I want to join to table1 based on
table2.datetime between table1.datetime_start and table1.datetime_end

Unfortunately, I don't know how to set this up as an index; at the very worst I'd like to be able to index the date portion of the datetime. I could do this by creating an extra column in table1 w/ just the date portion of the data and index that, but that violates the normalcy rules.

question 1: how can i speed up these types of queries
question 2: are two seperate datetimes the best way to store timespan data?

Options: ReplyQuote

Written By
Index on datetime type
November 17, 2010 09:57AM
November 18, 2010 10:22PM

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.