SQL

Select today's MySQL records


Problem

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.



Solution

Let's assume we've got a tabel called test with the following records:

id created_at
1 2016-10-17 09:45:10
2 2016-10-18 09:55:10
3 2016-10-17 03:15:15

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()

By using 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. curdate() returns 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:

select_type type rows filtered Extra
SIMPLE ALL 3 100.00 Using where

The 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:

select_type type rows filtered Extra
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.

Comments




Please sign-in to post a comment