How can I efficiently search for all records from a given month, or a given year?

Question

We have a couple queries that involve datetime fields. In particular, we are checking for things like

SELECT field1, field2, ..., field20 FROM table WHERE MONTH(startDate) == 1
SELECT field1, field2, ..., field20 FROM table WHERE YEAR(startDate) == 2014

So for example, maybe I want all January records (regardless of year), or I want all records from a particular year, or maybe I want all records for January 2014.

I have an index on the startDate column, but EXPLAIN tells me that both queries are doing full searches on the table because I'm using the MONTH and YEAR functions.

How can I make these queries more efficient?
Our table is very small, with about 100k records, but it still takes about 200-300 ms to complete.

Answer 1

Why not the classic way to avoid functions

WHERE startDate BETWEEN '2014-01-01' AND '2014-12-31';

or

WHERE startDate LIKE '2014-01-%' ;