mysql下载哪个版本好2022mysql查询⽇期胜负，哪个mySQL⽇期查询性能更好？
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.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。