Ratings Query Structure and Process Economy

Question

Hi All,

I am trying to display overall ratings on the front of my site and although my site and DB are small now, I think this query and process might bog things down when this table gets large.

Right now, I have ratings employed that are easy because my query is saying: find all records for this ID, pull all ratings and average them.

What I now want to do is a query that says: Find ALL records regardless of ID, sort by ID, average any existing "RATINGS" for each record, hold the rating somewhere with associated ID then have a cutoff at the top 10 records.

This middle area in the real world would be like "scratch paper" and I don't know how to address this middle ground. Well, I do, but all those remaining results that fall outside of the ten that I want seem like a wasted process??

I don't know.. I recall a guy talking about memcache or something once. Is that the "scratch pad" i am looking for??

Thanks, Rob

Answer 1

Memcached certainly could be used for that. Its a memory based (so fast) way to store and data, and is widely used.

Being ram based, its not persisted if your system restarts, so the most common practise is to use it alongside a relational database to store data/structures that are relatively expensive to produce, this saves constantly recreating them - certainly caching results of complicated database queries as you suggest is a common use.

Memcached runs as a service on your server. For PHP to be able to access it, you need install the pecl memcache extension. In your code, you instantiate its memcache class and then use the set() and get() methods to save and load data under known keys. It will automatically serialize/deserialize objects, so you can set and get native php/objects/arrays etc.

Slight aside: I agree with what you are saying, averages and overviews are often slow because of the amount of data to churn. But I'd be wary of optimising this too much before its an issue - you may change other things before this is an issue that mean you no longer have to solve this, or you may never have enough users/data for this to be a problem, and its often not the things you think that impact performance most anyway, which you can't know until you have issues.

I'd argue that your time is better spent building traffic/users etc first!