Craig Matthews wrote:
> Just FYI, it's a date time field. I would like to make sure I only get records where this datetime
> field is same as curdate(), i.e., today.
Compare the following two queries on a 250000 record test table filled with datetime-records for the first halfyear of 2005 (the dt column is indexed):
EXPLAIN
SELECT id FROM foo
WHERE dt BETWEEN
DATE_FORMAT(CURDATE(), "%Y-%m-%d 00:00:00") AND
DATE_FORMAT(CURDATE(), "%Y-%m-%d 23:59:59")\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: range
possible_keys: dt
key: dt
key_len: 9
ref: NULL
rows: 1373
Extra: Using where
EXPLAIN
SELECT id FROM foo WHERE DATE_FORMAT(dt, "%Y-%m-%d") = CURDATE() \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 250000
Extra: Using where
Although the second one certainly looks simpler, all it gives you is a full table scan, while the first one will only have to examine about 1400 records. You can expect quite some speed increase (0.04 sec vs 0.86 sec)!
--
felix
Please use
BBCode to format your messages in this forum.