If you want an index to be used and the query not to do a table scan:
WHERE timestamp >= CURDATE()
AND timestamp < CURDATE() + INTERVAL 1 DAY
To show the difference that this makes on the actual execution plans, we'll test with an SQL-Fiddle (an extremely helpful site):
CREATE TABLE test --- simple table
( id INT NOT NULL AUTO_INCREMENT
,`timestamp` datetime --- index timestamp
, data VARCHAR(100) NOT NULL
DEFAULT 'Sample data'
, PRIMARY KEY (id)
, INDEX t_IX (`timestamp`, id)
) ;
INSERT INTO test
(`timestamp`)
VALUES
('2013-02-08 00:01:12'),
--- --- insert about 7k rows
('2013-02-08 20:01:12') ;
Lets try the 2 versions now.
Version 1 with DATE(timestamp) = ?
EXPLAIN
SELECT * FROM test
WHERE DATE(timestamp) = CURDATE() --- using DATE(timestamp)
ORDER BY timestamp ;
Explain:
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF
1 SIMPLE test ALL
ROWS FILTERED EXTRA
6671 100 Using where; Using filesort
It filters all (6671) rows and then does a filesort (that's not a problem as the returned rows are few)
Version 2 with timestamp <= ? AND timestamp < ?
EXPLAIN
SELECT * FROM test
WHERE timestamp >= CURDATE()
AND timestamp < CURDATE() + INTERVAL 1 DAY
ORDER BY timestamp ;
Explain:
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF
1 SIMPLE test range t_IX t_IX 9
ROWS FILTERED EXTRA
2 100 Using where
It uses a range scan on the index, and then reads only the corresponding rows from the table.