mysql unix时间加索引_MySQL：UNIX_TIMESTAMP函数不走索引问题

关于时间戳的类型转换

取当时的时间戳函数

mysql> SELECT UNIX_TIMESTAMP(TIMESTAMP(DATE(SYSDATE()))),UNIX_TIMESTAMP(NOW());

+--------------------------------------------+-----------------------+

| UNIX_TIMESTAMP(TIMESTAMP(DATE(SYSDATE()))) | UNIX_TIMESTAMP(NOW()) |

+--------------------------------------------+-----------------------+

| 1464537600 | 1464575169 |

+--------------------------------------------+-----------------------+

1 row in set (0.00 sec)

使用UNIX_TIMESTAMP函数，只走了member_id的索引

mysql> explain SELECT COUNT(1) AS COUNT

-> FROM gt_member_login_log

-> WHERE member_id = 528656

-> AND login_time BETWEEN UNIX_TIMESTAMP(TIMESTAMP(DATE(SYSDATE()))) AND UNIX_TIMESTAMP(NOW());

+----+-------------+---------------------+------+----------------------------------+-----------+---------+-------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------------+------+----------------------------------+-----------+---------+-------+-