Data and Query Caching Clarification Needed


I’ve never actually used data or query caching before so am a little unclear.

My scenario is this…

I have an sql statement that returns a data set of questions and associated answers for the users quiz session. The data set must remain the same for the entire session as each question must only be asked once so I can’t go back to the db each time.

I want to display one question and associated answers per page and then I want to only allow the user to page forward NOT backwards. I’m thinking ajax is the way to go but again am unclear about how ajax would actually work. Does the paging happen client side through the record set? If that’s the case then this might be simpler than I’m imagining.

My question is twofold. Will data caching or query caching suit my needs and if so which one? Does the caching relate to the whole app or just the particular user? If it’s just the user how does yii ensure that the cache stays with the user?

I’m guessing maybe I have to write a widget to handle the display and paging but again I’m not sure.

Any help or advice would be much appreciated.



Query cache is a kind of data cache. Query cache is for query results and data cache for PHP variables(including arrays). If you are concerned with db related data, generally it’s appropriate to use query cache.

We set an unique ID string for each cache entry. It must be unique application wide. And in query cache, the sql statement is treated as the unique ID. Tiny bit of difference between sqls will result in different cache entries.

So when your sql contains a phrase for filtering user, the resulting cache entry will only be applied to that user. And when the sql is just for retrieving questions and answers, then the cache entry will be shared among all users.

But I think you would be better forget about the caching for the moment. You have to build an app that works before you consider performance tuning. Build your app retrieving data from db each time when you need it. Caching can be introduced at a later stage of development.

At any time, we can’t expect that there is a valid cache entry for a specific data. The app should work fine even when there’s no valid cache entry at all.

Thanks for the response.

The query caching isn’t so much a matter of performance but is an integral part of how the application will work.

Maybe an example might help…

If user A generates a test with 10 questions (random) and there associated answers then every time the user moves from one question to the next (one question per page) he must be using the same set of data that was returned when the test was first generated for him. That way all questions are assured to be unique for that particular test. I can’t just grab one question and associated answers every time because uniqueness is not guaranteed unless I keep track of the questions the user has already been asked which seems like an unnecessary overhead and bad design to me.

It seems like you’re saying that query cache will be unique between users so when user B generates a test at the same time as user A he will get different (random) set of questions. That’s the way the sql works at the moment and it works well.

I’m trying to work out the best way and the most appropriate way to hold the data returned the first time the test is generated. I’m thinking that with query caching the each time the sql is ran that rather than grab a new set of questions it will page through the cached query. Again that’s the way I understand it but I may be incorrect hence the reason for my question.

Hope that helps.



If you need some persistent data among sessions, why don’t you save it to (db|file|session)? It can be cached. But it’s not appropriate to store some persistent data directly in cache.


These are few words from the guide:

"Caching is a cheap and effective way to improve the performance of a Web application. "

"Note: By definition, cache is a volatile storage medium. It does not ensure the existence of the cached data even if it does not expire. Therefore, do not use cache as a persistent storage (e.g. do not use cache to store session data)."

I don’t really understand your answer to be honest. How does it relate to the problem I outlined? Maybe I didn’t explain my need properly. I can try again if you want.


Ah, sorry. I’ve just edited the previous post.

In short, you should not expect something from cache other than "performance".

As to your problem, there’s no magic way to do it, I think.

  1. Generate a random set of questions and answers for a user.

  2. Save it to some media … it can be a db table or a file. Or maybe the user session.

  3. Access the stored data in the subsequent sessions. Maybe updating it.


Thanks for the response softark

So based on your answer caching is not the answer to my problem so what about this idea (just off the top of my head).

Because the user may have 100 questions with 4 possible answers for each I don’t really want to store all that data in the database when it’s already there. My thought is to just store the id’s of the questions with there corresponding answers then for each page just grab the next set of ids and use them in an sql statment. Or maybe store the ids in a session? Would storing them in a session be quicker than storing them in db?

Just a few random thoughts off the top of my head.

Any feedback would be appreciated.



Yeah, it sounds a decent solution to me.

(You can use a query cache when you retrieve a question and its corresponding answers from the given id.)

I would prefer storing ids in session, because it doesn’t need garbage collection.

Thanks for the feedback. I’ll have to get my thinking cap on now :D