MySql and the Art of Sql

I’ve been reading the The Art of SQL and it makes some very sensible suggestions regarding query performance.

Here’s somewhere I’ve implemented 1 of the techniques.

The task: return records for this month.

Usually I’d write something like

select * from results where month(dateadded) = month(now()) and year(dateadded) = year(now())

The problem with this is that 2 functions (month() and year()) need to be applied to every row to filter the results, this obviously requires overhead.  Also we’re missing out on any indexes that might be on the dateadded field.

A better approach is to use

select * from results where dateadded >= DATE_FORMAT(now() ,'%Y-%m-01')

With this approach 1 function is run to find the first day of the month, and if the dateadded field is indexed we’ll benefit from this.

This entry was posted by nerdboy on Thursday, July 2nd, 2009 at 10:48 am and is filed under MySql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response below, or trackback from your own site.

Show/Hide Comments (0)