MySQL Forums
Forum List  »  Performance

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

Written By
optimizing/indexing date column
November 15, 2011 10:06PM
November 15, 2011 10:19PM
November 15, 2011 10:30PM
November 15, 2011 11:56PM
November 17, 2011 01:46AM
November 17, 2011 10:14PM
November 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.