Let me assure you that DATETIME is the worst choice ever... because it need 8 bytes per record... TIMESTAMP uses only 4
...but TIMESTAMP has a special behaviour which may not be wanted in all cases: the first TIMESTAMP column of a table is updated automatically when _any_ column is changed in the row. Another issue is the range: a TIMESTAMP column can store values between 1970 and sometime in 2037, while a DATETIME column can store values in the range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
DATE uses only 3, so does TIME YEAR is the smallest... 1 byte.
Yes, but none of these can store a date/time combination, thus they are not relevant in this case.
You can do:
SELECT * FROM activities WHERE act_date >= NOW()
That is correct.
But for optimization... you should not compute in WHERE... so this is
faster.. .but there are 2 queries:
SET @this_moment = NOW();
SELECT * FROM activities WHERE act_date >= @this_moment
This is not correct. Of course you can not avoid computing in the where clause. The issue when it comes to optimization is that you should avoid a function call or other computations on the column you are testing, if possible, to enable the server to use an index on that column, if an index is defined.
For example, if there was an index on the act_date column, this query would use the index:
SELECT * FROM activities WHERE act_date >= NOW()
..while these would not, because of the computation on the act_date column which must be executed for each row:
SELECT * FROM activities WHERE (act_date + interval 1 day) >= NOW()
SELECT * FROM activities WHERE left(act_date,10) >= CURDATE()
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
