homepage Welcome to WebmasterWorld Guest from 54.167.75.155
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
index on date fields?
jbroder




msg:4367510
 9:52 pm on Sep 26, 2011 (gmt 0)

Hi,

In a MySQL database, I have two big tracking tables. Each has a date field. One date field has data type = timestamp, and the other has data type = datetime.


Both tables get thousands of inserts all day tracking site traffic, and both have infrequent (once a month) selects that are in the form of

SELECT * FROM table WHERE month(datefield) = XX and year(datefield) = #*$!X

I am the only one who runs the selects. The selects take a long time, so I am thinking about creating an index on the datefield.

Questions about this:

1. Is it useful/customary to make an index on a date field? I think I read somewhere this is unnecessary, but now I can't find much information about it.

2. How much slower are the inserts going to be if there is an index? I know they are going to be slower, so I wonder if it is advisable to make the index given that I am the only one to benefit from the performance boost?


thanks for any help.

 

arms




msg:4367702
 9:58 am on Sep 27, 2011 (gmt 0)

I wouldn't create an index just for that, but maybe create an index when I need it then delete it when finished, but also I'd look at your query, you're creating an overhead using 2 functions (year(), month()) on a table scan, try using a between statement ie

SELECT * FROM table
WHERE datefield BETWEEN '01-Mar-2011' and '31-Mar-2011 23:59:59'

if you think about it, it's exactly the same request as

SELECT * FROM table WHERE month(datefield) = 3 and year(datefield) = 2011

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved