Jump to content

The age old problem - storage space vs. speed


Recommended Posts

Ok, currently I have this query:

     (SELECT COUNT(*) FROM inquiries WHERE iduser = '$id') AS icount, 
     (SELECT COUNT(*) FROM responses WHERE iduser = '$id') AS rcount, 
     (SELECT IFNULL( SUM( total_value ) / SUM( total_votes ) , 0 ) FROM ratings, responses WHERE responses.iduser = '$id' AND responses.idresponse = ratings.id AND ratings.total_votes > 0) AS avgscore"


With 3 rows in 'users', 35 in both 'ratings' and 'responses', and 29 in 'inquiries', this query took about 1/1000th of a second on my machine. These values are used in an equation in php, and must are fetched every time a user's profile page is loaded.  With many more rows in these tables, would this query be very slow? I'm wondering whether or not to add a field to one of the tables to hold the calculated score when an inquiry/response row is added, or when a rating row is updated. Or should I just keep things like this? It's the age old storage space & feasibility vs. speed

Link to comment
Share on other sites

Does the following produce the same results faster?


    sum(inquiries.iduser IS NOT NULL) AS icount,
    sum(response.iduser IS NOT NULL) AS rcount,
    ifnull(sum(ratings.total_value) / sum(ratings.total_votes), 0) AS avgscore
FROM users
    LEFT JOIN inquires ON users.id = inquries.iduser
    LEFT JOIN responses ON users.id = responses.iduser
    LEFT JOIN ratings ON responses.idresponse = ratings.id
WHERE users.id = '{$id}'


Speed is probably more important when dealing with the dynamics of the Internet.  Storage size is less dynamic and easier to control than bandwidth traffic.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.