在MYSQL中指定SUBTIME间隔是否会阻止查询不必要地遍历整个表？

Question

Please tell me, if I specify an interval like such: subtime(now(), INTERVAL 1 day) inside a MySQL SELECT query while having a proper datetime column to use as reference - will this prevent from the query to look through the entire table (over 100,000 records in my case) each time it runs but rather look through records made only the past 24 hours? Is DESC order needed for the datetime table or such? Also, if I have SUM(column) in the query, will it also run only for the interval specified?

Edit: If I just would like to use the above mentioned SUM to sum a column where there only are integers of value "1" - would it be better to simply check how many rows the SELECT query returns with mysql_num_rows - is it more efficient in combination with the time interval setting?

Thank you!

Answer 1

It will in fact prevent MySQL to go through the whole table but not if you just subtime() in the SELECT-part. Instead you have to do something like this:

SELECT * FROM myTable 
WHERE myDateCol BETWEEN DATE_SUB(now(), INTERVAL 1 day) AND now()

The query will now select only rows one day old. Add a B-TREE index on myDateCol to speed things up:

ALTER TABLE myTable ADD INDEX myIdx USING BTREE (myDateCol)

See MySQL doc on that topic