Posts Tagged ‘sql’

MySql and the Art of Sql

Posted on July 2nd, 2009 • Filed under MySql • No Comments

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.