0

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.

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-%' ;
  • Thinking about it, our users usually search by year or by year and month, rather than month alone, so it would be better to write efficient queries when I can, and then leave that full-search for later since it is not a very common use case anyways. In that case, BETWEEN would be enough. – That Umbrella Guy Jan 9 '14 at 21:42
  • Keep in mind that between is just comparison when MONTH() and YEAR() are functions and causes alot of conversions and transformations. Also between needs appropriate index for efficient search. – Kondybas Jan 9 '14 at 22:04
  • @Mihai What happens if the month does not have 31 days? For example, would '2014-02-01' and '2014-02-31' cause unexpected issues? – That Umbrella Guy Jan 9 '14 at 22:11
  • Since you have to write the dates manually anyway,why would anyone search for day that does not exist?But AFAIK this is the only way to get an index use,maybe someone knows more. – Mihai Jan 9 '14 at 22:13
  • @Mihai They are providing the month and year only, the day is arbitrary. Plus, it would be terrible if they could search Feb 31 and break the application. – That Umbrella Guy Jan 9 '14 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.