CODEWITHSUNDEEP
phpmysqlperformancecaching
rinserepeat
rinserepeat

Reputation: 165

store temporary results in a seperate database table

I was wondering what would be a good approach to mitigate expensive queries to a database. For instance queries that use loads of records, joints and calculations.

So i thought I would perform these expensive queries once every 20 minutes (or so) and give users a stored result from a special table that stores the results of these expensive queries. Would this be a good approach, or would you go for a flat file sort of caching system that stores the results from the database? I read that the normal filesystem is slower.

If anyone could enlighten me on this subject I would really appreciate it.

Upvotes: 0

Views: 116

Answers (1)

Keith
Keith

Reputation: 310

You need to cache the data, its just a matter of where and that will also be based upon the size of the dataset.

Memcache as Daryl stated is a very good solution. Dependent upon what is accessing this data and your framework on the backend, it could be stored inside an application based cache that will only issue the query once every x minutes and used cache results in the meantime.

If you want to go the straight sql route, you would employ a data warehouse concept.
You would create a flattened schema that is used for reporting instead of transaction based processing. You will reduce the number of joins and denormalize the data to an extent such that the query will be much quicker...but it will be stale data for a specific period.

You can set up a service/scheduled task/database scheduled task to issue your query, purge the existing flattened table, and repopulate it with your new results.

Upvotes: 1

Related Questions