SQL SUM question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tbrown1
Forum Newbie
Posts: 17
Joined: Wed Oct 25, 2006 10:58 am

SQL SUM question

Post by tbrown1 »

Ok I think I know the answer to this but I need a little reassurance. Is it truly faster to use "SELECT SUM(votes) FROM table" instead of
"SELECT * FROM table" and then adding the results using a while loop. I can see that it should be but I am not sure if using the sum command puts extra load on the database that would maybe be better handled by the webserver

Any thoughts?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It puts a bit of load on the database, but that is offset by it only needing to return a smaller result set to you. It's more work to sum it yourself.
tbrown1
Forum Newbie
Posts: 17
Joined: Wed Oct 25, 2006 10:58 am

Post by tbrown1 »

Ok thankyou that's what my opinion was but I needed someone to confirm it for me
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Think of it this way. Your web server is working to serve pages. The code engine (PHP, ASP, CF, whatever) is working to handle the instructions presented by the code. The Database Server is also working to server data, and it does it pretty well. The more work your database server can reasonably do, the less work your web server has to do with data. So even if there is a performance loss on the database server by handling an instruction that is more complex than 'SELECT this', it is still, overall, going to be less of a performance hit that having your code engine, which is already working a ton, handle the data that is returned by the database server.

Besides, the database servers have those functions built in so they can be used. Why not use them?
tbrown1
Forum Newbie
Posts: 17
Joined: Wed Oct 25, 2006 10:58 am

Post by tbrown1 »

Good point
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Actually using the SUM in your example puts far LESS strain on the database server than grabbing every single record and field from the table.

Code: Select all

SELECT SUM(votes) FROM table
Will cause the database server to add all of the vote fields together and send one record to PHP. This will use far less processor time on the database side and transfer only a very tiny amount of data from the database server.

Code: Select all

SELECT * FROM table
The above will cause the database server to collect every single record with ALL fields to send to PHP. This means the database server has to use far more processor time collecting the full record sets for the ENTIRE table as well as allocating memory for every recordset. If the table has many fields and many records this can be very slow and memory intensive.

If all you need are the votes you can use...

Code: Select all

SELECT votes FROM table
...and then add up each votes record element through PHP. But even then the database server will be doing far more work than if you just use SUM.
Post Reply