MySQL Cache and Date Functions

Question

I once read on a performance blog that it’s better to use PHP date functions to set dates in a MySQL query instead of using mysql date functions like curdate (), because mysql can cache the query or result or something like that, Someone can anyone understand this? Does he have any water or is it unreasonable?

Example:

$query = 'SELECT id FROM table WHERE publish_date = \''.date('Ym-d').'\''; 

against

 $query = 'SELECT id FROM table WHERE publish_date = CURDATE()'; 

Answer 1

Any function containing CURDATE() will not be cached. A source

Hardcoding date should be cached as far as I can tell. Although you might consider using prepare , instead of planning the lines in your query (for common sense and security).

Answer 2

It is pretty simple. The MySQL server does not see your PHP code, so it will receive one of them:

 SELECT id FROM table WHERE publish_date = '2010-01-18'; SELECT id FROM table WHERE publish_date = CURDATE(); 

He will also not read your intentions. For MySQL, '2010-01-18' is a string and is deterministic: its value is always "2010-01-18". However, CURDATE() not deterministic: its value changes depending on the date it was started. Thus, the first one is cacheable, and the second is not.

Answer 3

I personally prefer the first path, because it gives a clear chapter on server time (time zone), my mysql server will probably be 10 hours earlier when promised :)

localtime in your PHP script will be applied in SQL