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'
解决方案
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.