时间戳记为int字段，查询性能 [英] Timestamp as int field, query performance

问题描述

我将时间戳存储为int字段.在大表上，由于我正在使用mysql函数FROM_UNIXTIME，所以在日期插入行花费的时间太长.

I'm storing timestamp as int field. And on large table it takes too long to get rows inserted at date because I'm using mysql function FROM_UNIXTIME.

SELECT * FROM table WHERE FROM_UNIXTIME(timestamp_field, '%Y-%m-%d') = '2010-04-04'

有什么方法可以加快查询速度?也许我应该对使用timestamp_field >= x AND timestamp_field < y的行使用查询?

Is there any ways to speed this query? Maybe I should use query for rows using timestamp_field >= x AND timestamp_field < y?

谢谢

已编辑此查询效果很好，但是您应该注意 timestamp_field上的索引.

EDITED This query works great, but you should take care of index on timestamp_field.

SELECT * FROM table WHERE 
timestamp_field >= UNIX_TIMESTAMP('2010-04-14 00:00:00')
AND timestamp_field <= UNIX_TIMESTAMP('2010-04-14 23:59:59')

推荐答案

使用

Use UNIX_TIMESTAMP on the constant instead of FROM_UNIXTIME on the column:

SELECT * FROM table
WHERE timestamp_field
   BETWEEN UNIX_TIMESTAMP('2010-04-14 00:00:00')
       AND UNIX_TIMESTAMP('2010-04-14 23:59:59')

这可能更快，因为它允许数据库使用列timestamp_field上的索引(如果存在).当您使用非可精函数时，数据库无法使用索引列上的FROM_UNIXTIME.

This can be faster because it allows the database to use an index on the column timestamp_field, if one exists. It is not possible for the database to use the index when you use a non-sargable function like FROM_UNIXTIME on the column.

如果timestamp_field上没有索引，则添加一个.

If you don't have an index on timestamp_field then add one.

完成此操作后，您也可以尝试选择所需的列而不是使用SELECT *来进一步提高性能.

Once you have done this you can also try to further improve performance by selecting the columns you need instead of using SELECT *.

这篇关于时间戳记为int字段，查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！