Help! MySQL caches my queries

A question from alt.php.sql:

Php generates a query, and the result returned is from a cache, not from the actual mysql db. The data I receive is not up-to-date – to changes I make during this time.

I have tried using mysql_unbuffered_query, but doesn’t help. I have finally come to the solution. At the end of list of fields following SELECT statement, I entered rand() function which causes that every query is different from the previous… Looks like this:

SELECT field1,field2,…,rand() FROM …

But this solution is ugly and very unproffesional.
Do you have any better idea?
How to stop php/apache from buffering/caching mysql query results?

Query caching is a MySQL-level setting; PHP and Apache have nothing to do with it.

You have two options. One is to stop query caching entirely by setting query_cache_type = 0 or query_cache_type = OFF in MySQL’s configuration file. See MySQL documentation on the subject.

To change this setting, you can edit the configuration file manually or run a simple query:

SET GLOBAL query_cache_type = 0;

If you have no access to MySQL’s configuration file or have insufficient rights to run a SET GLOBAL query, you can add an SQL_NO_CACHE clause to your queries to request that MySQL actually run the query rather than return a cached result:

SELECT SQL_NO_CACHE * FROM myTable;

Again, see MySQL documentation on the subject.

There is also the option of setting query_cache_type = 0 or query_cache_type = OFF on a per-session basis:

SET SESSION query_cache_type = 0;

but for a Web application it will probably have a performance penalty compared to using SQL_NO_CACHE, because you will have to run this query from every script.

This entry was posted in Answers, Technology. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *