哪个mySQL日期查询性能更好? [英] Which mySQL date query performs better?

问题描述

我有一个查询，可以用两种不同的方式表示相同的结果.出于性能或其他原因，哪个更好?

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

第一个查询:

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

第二个查询:

SELECT post_id FROM posts
    WHERE YEAR(post_date)=2010

谢谢.

在建议进行基准测试后，我进行了一些搜索和测试.由于计算机上的某些问题，我的测试不是基准测试，但它们给了我一些想法.

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.

我已经测试了我的4000行表，但没有太大的区别.在总查询时间为0.09时，BETWEEN命令仅比YEAR(post_date)多0.01-0.02秒.似乎使用YEAR(post_date)既可以提高性能，又可以提高可用性.

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.

我在搜索时了解到这一点；如果小时或分钟不是那么重要，则可以这样使用BETWEEN:

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'

推荐答案

如果您在post_date上有一个索引(如果您希望经常运行此查询，我建议您这样做)，则BETWEEN查询可以使用它.将列放入函数(YEAR(post_date))后，MySQL不再使用该列的索引，因此它必须遍历所有行(称为full table scan).

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

使用查询查询EXPLAIN SELECT的输出(

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.

当然，请对代码进行基准测试并亲自查看-但通常，在WHERE中使用函数的速度较慢.

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

这篇关于哪个mySQL日期查询性能更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！