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()';
MySQL determines the queries to cache by examining the query_cache_type variable. Setting this value to 0 or OFF prevents caching or retrieval of cached queries. You can also set it to 1 to enable caching for all queries except for ones beginning with the SELECT SQL_NO_CACHE statement.
To make sure MySQL Query Cache is enabled use: mysql> SHOW VARIABLES LIKE 'have_query_cache'; To monitor query cache stats use: mysql> SHOW STATUS LIKE 'Qcache%';
The MySQL query cache is a global one shared among the sessions. It caches the select query along with the result set, which enables the identical selects to execute faster as the data fetches from the in memory.
MySQL NOW() Function The NOW() function returns the current date and time. Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS. uuuuuu (numeric).
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With