optimizing/indexing date column
Date: November 15, 2011 10:06PM
I have a 40million record set table, and I need to query it a lot based on the date. There's two date columns (among the rest of the data)
and it's basically a date range, EX:
startdate = 2001-01-01
enddate = 2001-02-01
I need to query for all records that a day fell in between that range. Say 2001-01-15.. So I use the query
WHERE startdate >= '2001-01-15'
AND enddate <= '2001-01-15' ;
That query takes 91-92seconds to run..
I tried making those two columns indexes:
CREATE INDEX startdate_idx ON history (`startdate`);
CREATE INDEX enddate_idx ON history (`enddate`);
Each creation took about 200seconds to build.
Afterwards I try the same SELECT query mentioned above, and it takes ~ 490seconds!
I don't know if i did something wrong building that index. I usually just use primary key indexes and foreign key constraints.
I copied 100k records to a tmptable and I did tests on it, turning the DATE into an INT - helped a little bit. But making the DATE an index cut the query time down in 1/5th.. I don't know why it had the exact opposite effect on my larger testing table.
Any suggestions or advice??
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.