I find that if I setup a int column stored millionsecond timestamp value to cast as millionsecond type, when I use it in filter conditions ,such as recent 1 month . the sql will be translated like this :
where
FROM_UNIXTIME(spam.create_timestamp / 1000) >= STR_TO_DATE(
CONCAT(
DATE_FORMAT(DATE_ADD(NOW(6), INTERVAL -1 month), '%Y-%m'),
'-01'
),
'%Y-%m-%d'
)
)
** KEY idx_create_timestamp (create_timestamp) USING BTREE,**
this index will not be used
explain SELECT spam.type AS type, FROM_UNIXTIME(spam.create_timestamp / 1000) AS create_timestamp, FROM_UNIXTIME(spam.finish_suspect_timestamp / 1000) AS finish_suspect_timestamp, FROM_UNIXTIME(spam.first_pull_timestamp / 1000) AS first_pull_timestamp FROM spam WHERE ( FROM_UNIXTIME(spam.create_timestamp / 1000) >= STR_TO_DATE( CONCAT( DATE_FORMAT(DATE_ADD(NOW(6), INTERVAL -1 month), '%Y-%m'), '-01' ), '%Y-%m-%d' ) ) AND ( FROM_UNIXTIME(spam.create_timestamp / 1000) < STR_TO_DATE( CONCAT( DATE_FORMAT(DATE_ADD(NOW(6), INTERVAL 1 month), '%Y-%m'), '-01' ), '%Y-%m-%d' ) );
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | spam | ALL | NULL | NULL | NULL | NULL | 1833512 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+