CODEWITHSUNDEEP
phpmysqlcaching
stov01
stov01

Reputation: 23

MySQL cache, do not flush on update until 10 minutes old

Is it possible to make it so that MySQL does not flush the cache when the table updates and holds that cache for at least 10 minutes before cache is flushed?

Basically, I'm caching a query that analyzes about 200k+ records and returns 10 rows of data. The query takes about 1.5 - 3 seconds. I'm using SQL_CACHE to cache that query, which after the first time takes 0.007(+-) seconds. The table updates very often (multiple times in less than a minute). So I want to make MySQL hold the cache for at least 10 minutes before it flushes that cached query to recalculate.

By the way, the query data is unique to each user on the site.

I'm using the Codeigniter framework, I know they have a built-in query caching feature however it's file-system based. Which is why I prefer MySQL's caching feature...

Thanks for all help guys!

Upvotes: 1

Views: 1413

Answers (1)

Nick ODell
Nick ODell

Reputation: 25344

Three things cause things to be removed from the MySQL cache:

  1. Not enough space
  2. The data behind the query changed
  3. The MySQL process restarts

You've got a fair bit of control over #1, because you can change the cache size. However, other queries can still take up cache space. I don't know how important controlling #2 is. You might want to use SELECT SQL_NO_CACHE to prevent those from entering the cache.

From the sounds of things, MySQL doesn't give you what you need to cache things the way you want to.

Have you considered optimizing the query? On that note, could you post the actual query?

Upvotes: 0

Related Questions