MySQL performance with date functions

Imagine you would have to fetch all rows from a MySQL table that were created in a certain month. This could be, for example, for creating archive pages in for a blog. Using SQL, this is fairly easy. MySQL comes with extended date and time support. You might be tempted to execute the following SQL statement:

SELECT * FROM blogs WHERE YEAR(created) = 2008 AND MONTH(created) = 12;

This would work fine for small data sets. However, as your blog keeps growing, row count increases and performance will decrease. The problem with a query like the above is that it is impossible to use indexes because you use functions in the WHERE statement. These functions can’t use the index and thus have to be evaluated for every row in the table.

Fortunately, this query can easily be rewritten to use a range search and thus make use of indexes possible:

SELECT * FROM blogs WHERE created BETWEEN "2008-12-01" AND "2008-12-31";

If you have an index on the created column, it can be used by MySQL and the query will be executed much faster.

Using PHP’s build-in date() function, you can easily create this statement:

$start_time = "$year-$month-01";
$end_time = date('Y-m-t', strtotime($start_time));
$db->fetchAll('SELECT * FROM blogs WHERE created BETWEEN "' .
               $start_time.'" AND "'.$end_time.'"');

Have any questions? Feel free to post them in the comments.