Most Efficient Way of Selecting Row by Month/Year?

I have a page where I only select the rows from my database that are in a particular month/year. The variable sent to each page is in YYYY-MM format.

To do my select query, I just did:

DATE_FORMAT(,‘%Y-%m’) = ‘2009-12’

It works fine, I’m just making sure that this isn’t too resource intensive. Is there a better way that uses less resources in the query?


yes, there’s a better way

  1. make sure that is indexed

  2. use a range test –

WHERE video_date >= '2009-12-01'
  AND  < '2010-01-01'


That’s what I was afraid of. So I’d have to take the YYYY-MM variable, add an “01” as the day and run it through some PHP coding to figure out the plus one on the month.

If you know I quick solution for adding a month I’m all ears.


adding a month in mysql or in php?

I ended up going with PHP.

$dateday = $date .'-01';
$datefuture = date("Y-m-d", strtotime(date("Y-m-d", strtotime($dateday)) . " +1 month"));

That gives me my one-month span for the query.


Just to clarify:

WHERE video_date >= ‘2009-12-01’
AND < ‘2010-01-01’

That date format works properly with datetime columns, correct? It’s working, just want to make sure it isn’t missing anything from the first day due to time.