Selecting records from a specific moment is a rather common requirement. It's pretty simple too, but there's one common mistake a lot of people make.
Let's assume we've got a tabel called
test with the following records:
Assuming today's date is the 17th of October, you'll find lots of people writing the query like this:
SELECT * FROM test WHERE date(created_at) = curdate()
date on our column, we can easily get rid of the time and thus compare the actual day with today's date. The result is okay, but the query is slow.
Why? We are using a function to modify the value in our database. Before MySQL can compare anything, it has to convert all values. If you just have a few hundred records that will take no time, but if your system gets bigger, you'll eventually run into performance problems. If you want to make sure MySQL can use the index you've created on the column, write your query like this:
SELECT * FROM test WHERE created_at >= curdate() AND created_at < curdate() + INTERVAL 1 DAY
What's happening here? We are using the column without a function and by that make sure MySQL can use the index on the column.
2016-10-17. The first part of the where condition makes sure we only selected records created today or later. In the second part, we are adding one day, getting
2016-10-18. Instead of
>= we are using
< now. That's important as we don't have to select records created on the 18th, only those that have been created before.
For those how would like to know a bit more about what's going on internally, here's some background information.
If you want to know a bit more about what MySQL does with your query, you can add
EXPLAIN to the beginning of your query and MySQL will show you an explain plan with some cryptic, but useful information.
EXPLAIN SELECT * FROM test WHERE date(created_at) = curdate()
If you execute that query you'll see table, the one below is simplified:
type column clearly states, that it had to read all rows, something we want to avoid. Now the same with the second query:
EXPLAIN SELECT * FROM test WHERE created_at >= curdate() AND created_at < curdate() + INTERVAL 1 DAY
The explain plan looks a bit different now:
|SIMPLE||range||2||100.00||Using index condition|
We are no longer scanning through all rows, instead our index will return the 2 matching rows and we are done. Try that with a few milion records and you'll see a big difference.