Skip navigation links

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


Advanced Search

optimizing/indexing date column
Posted by: Chris T ()
Date: November 15, 2011 10:06PM

Hello,

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

SELECT *
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??

Thanks!

Options: ReplyQuote


Subject Views Written By Posted
optimizing/indexing date column 7216 Chris T 11/15/2011 10:06PM
Re: optimizing/indexing date column 3471 Mark Daly 11/15/2011 10:19PM
Re: optimizing/indexing date column 2683 Chris T 11/15/2011 10:30PM
Re: optimizing/indexing date column 2382 Mark Daly 11/15/2011 11:56PM
Re: optimizing/indexing date column 2048 Jørgen Løland 11/17/2011 01:46AM
Re: optimizing/indexing date column 1744 Chris T 11/17/2011 10:14PM
Re: optimizing/indexing date column 1325 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.