Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL cache and date functions

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()';
like image 595
dqhendricks Avatar asked Jan 18 '11 17:01

dqhendricks


People also ask

Does MySQL do caching?

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.

How do I view MySQL cache?

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%';

How does MySQL caching work?

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.

What function finds the current time and date in MySQL?

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).


1 Answers

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).

like image 173
Kendall Hopkins Avatar answered Oct 05 '22 23:10

Kendall Hopkins