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.