php and MySQL: 2 requests or 1 request?

Question

I'm building a wepage in php using MySQL as my database.

Which way is faster?

1. 2 requests to MySQL with the folling query.

   SELECT points FROM data;
   SELECT sum(points) FROM data;

2. 1 request to MySQL. Hold the result in a temporary array and calcuale the sum in php.

   $data = SELECT points FROM data;

EDIT -- the data is about 200-500 rows

Answer 1

How much data are we talking about? I'd say MySQL is probably faster at doing those kind of operations in the majority of cases.

Edit: with the kind of data that you're talking about, it probably won't make masses of difference. But databases tend to be optimised for those kind of queries, whereas PHP isn't. I think the second DB query is probably worth it.

Answer 2

It's really going to depend on a lot of different factors. I would recommend trying both methods and seeing which one is faster.

Answer 3

Since Phill and Kibbee have answered this pretty effectively, I'd like to point out that premature optimization is a Bad Thing (TM). Write what's simplest for you and profile, profile, profile.

Answer 4

The general rule of thumb for efficiency with mySQL is to try to minimize the number of SQL requests. Every call to the database adds overhead and is "expensive" in terms of time required.

The optimization done by mySQL is quite good. It can take very complex requests with many joins, nestings and computations, and make it run efficiently.

But it can only optimize individual requests. It cannot check the relationship between two different SQL statements and optimize between them.

In your example 1, the two statements will make two requests to the database and the table will be scanned twice.

Your example 2 where you save the result and compute the sum yourself would be faster than 1. This would only be one database call, and looping through the data in PHP to get the sum is faster than a second call to the database.

Answer 5

do it the mySQL way. let the database manager do its work. mySQL is optimized for such tasks

Answer 6

If you want to do it in one line, use a running total like this:

SET @total=0;
SELECT points, @total:=@total+points AS RunningTotal FROM data;

Answer 7

I wouldn't worry about it until I had an issue with performance.

Answer 8

If you go with two separate queries, you need to watch out for the possibility of the data changing between getting the rows & getting their sum. Until there's an observable performance problem, I'd stick to doing my own summation to keep the page consistent.

Answer 9

Just for the fun of it.

SELECT COUNT(points) FROM `data`
UNION
SELECT points FROM `data`

The first row will be the total, the next rows will be the data.

NOTE: Union can be slow, but its an option.

Could also do more fun and this supports you sorting the rows.

SELECT 'total' AS name, COUNT(points) FROM `data`
UNION
SELECT 'points' AS name, points FROM `data`

Then selecting through PHP

while($row = mysql_fetch_assoc($query))
{
    if($row["data"] == "points")
    {
        echo $row["points"];
    }

    if($row["data"] == "total")
    {
        echo "Total is: ".$row["points"];
    }
}

Answer 10

You can use union like this:

(select points, null as total from data) union (select null, sum(points) from data group by points);

The result will look something like this:

  point                total
   2                    null
   5                    null
   ...
   null                  7

you can figure out how to handle it.