MySQL cache and date functions

Question

I once read in a performance blog that it is better to use PHP's date functions to set dates in a MySQL query instead of using mysql date functions like curdate() because mysql can then cache the query or the result or something like that. Does anyone have any insight into this? Does it hold any water or is it baseless?

example:

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

vs

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

Faircloth

Answer 1

Any function containing CURDATE() will not be cached. Source

Hardcoding the date should still be cached as far as I can tell. Though you might want to consider using the prepare functionality instead of splicing strings into your query (for sanity and security sake).

Mella

Answer 2

It's quite simple actually. The MySQL server does not see your PHP code so it'll receive one of these:

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

It will not read your intentions either. For MySQL, '2010-01-18' is a string and is deterministic: its value is always '2010-01-18'. However, CURDATE() is not deterministic: its value varies depending on the date when you run it. Thus the first one is cacheable and the second one is not.

Scevo

yes, i know mysql cannot see my php and receives a string. i know it can cache this query for sure. i was unsure if it was possible for mysql to cache a query with curdate until the end of the day or something of that nature however. – Faircloth

@Faircloth Well, MySQL determines the validity of a cached result through the modification dates of underlying tables. It isn't more complex than that. – Walters

Answer 3

I personally preffer first way, because it give clear head about server time (time zone), my mysql server happend to be 10h earlier when promissed :)

localtime in your PHP script will apply in SQL

Frag

while not an answer to the question at hand, still a good point. – Faircloth