MySQL Forums :: Performance :: optimizing/indexing date column

Advanced Search

optimizing/indexing date column
Posted by: Chris T ()
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)

startdate DATE,
enddate DATE

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

FROM history
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??


Options: ReplyQuote

Subject Views Written By Posted
optimizing/indexing date column 19130 Chris T 11/15/2011 10:06PM
Re: optimizing/indexing date column 8808 Mark Daly 11/15/2011 10:19PM
Re: optimizing/indexing date column 6688 Chris T 11/15/2011 10:30PM
Re: optimizing/indexing date column 6087 Mark Daly 11/15/2011 11:56PM
Re: optimizing/indexing date column 5230 Jørgen Løland 11/17/2011 01:46AM
Re: optimizing/indexing date column 4365 Chris T 11/17/2011 10:14PM
Re: optimizing/indexing date column 3293 Rick James 11/18/2011 12:21AM

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.