Update MySQL Query Cache once per day?

[Spork52]

I want queries and their results to cache, but get updated once per day.

I was going to use PHP to add a sort of dummy date string to queries, so that they change every day:

"start of query WHERE ".date("d") = date("d")." AND rest of query"

But I don't want to kludge all my queries. There must be a better way to do this. E.g., can the MySQL cache be scheduled to update once per day?

 

[ingresman]

MYSql caching doesn't really store the results of queries.
I think to do what you wantto do you will have to run the query once and store the results of that query in a temp table. Subsequent enquires will use that table instead of the "live" data.
I supose you could wrap it all up in a stored proc so the client doesn't have an idea what's going on. The logic would be something like.

1.Get the current date and select from a table which has a name with those values as a suffix e.g. customers17022010 (12 Feb 2010 )
2.If that table doesn't exist it's the first time the table has been used so do the query against the live data and put the output into a table with the name derived above and return the data to the client.
3.If the table does exist, return that data in the table to the client.
You will need to have some kind of clean up job that runs dailly to get rid of old tables

or something like that

 

[Spork52]

Hmm. Could you clarify this: "MYSql caching doesn't really store the results of queries." From what I've read, caching stores the queries and results, and returns the cached results of any cached query unless a table in the query is modified.

 

[ingresman]

Yes, your correct. I've looked in my book and it does say if the text of the query is the same if will cache the query results.
I'd have a look in the maual and maybe do a search for the detail, it looks like you get it for free anyway.

 

[Spork52]

i read more of the MySQL documentation and noticed something I had missed before: the query cache won't work with queries that include CURDATE() and other date/time functions.

The queries I am working with all use date/time functions, so I guess I'm going to have to rewrite them with PHP date/time functions anyway to get them to cache.

 

[ingresman]

soory to be so long getting back.
The reason why CURDATE wuld be rejected is it will make the query dynamic every time. The query has to identical si depending on how many PHP dates you pass (which by the time they get to MYSQL will be translated to a literal) you might get quite a full cache.