Which mySQL date query performs better?

Question

Hello,

I have a query which can be expressed 2 different ways with the same results. Which one is better - for performance or other reasons?

First query:

SELECT post_id FROM posts 
    WHERE post_date BETWEEN '2010-01-01 00:00:00' AND '2010-12-31 23:59:59'

Second query:

SELECT post_id FROM posts
    WHERE YEAR(post_date)=2010

Thanks in advance.

---

After suggestion for benchmarking I have had some searchs and tests. My tests were not benchmarks because of some problems on my computer but they gave me some idea.

I have tested my 4000 rowed table and there was not an important difference. BETWEEN command was just more 0.01-0.02 sec than YEAR(post_date) at 0.09 total query time. It seems using YEAR(post_date) would be good for both performance and usability.

And I have learned that while searches; if hours or minutes are not so important, BETWEEN could be used like this:

SELECT post_id FROM posts 
    WHERE post_date BETWEEN '2010-01-01' AND '2010-12-31'

Answer 1

You can use the query profiler to see exactly how long each one takes:

http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

To be accurate, you'll of course want to test each one several times and make sure nothing else is running on the system.

Be sure to come back here with your answer :)

Answer 2

I agree with the other posters - simply try it, since you have both queries available. There's no better test than that.

If I had to guess (which is always a bad idea!) which is faster, then I would say, if post_date is indexed, then the first one would be faster since it can use the index to retrieve the matching rows. If there is no index, then any difference between the two will be marginal.

Answer 3

If you have an index on post_date (which I'd recommend if you want to run this query often), then the BETWEEN query can use it. Once you put a column into a function (YEAR(post_date)), MySQL no longer uses the column's index, so it has to go through all the rows (that's called a full table scan).

Check out the output of EXPLAIN SELECT with your queries (check this tutorial) and see what results you'll get - if there's a usable index, you should see the difference clearly.

Of course, benchmark your code and see for yourself - but in general, using functions in WHERE is slower.

Answer 4

As the others say, you can measure the relative performance yourself.

SELECT post_id FROM posts WHERE YEAR(post_date)=2010

is far more readable than the other, and unless performance is an issue I'd always go with that.