Performance difference between DB count and Array count

Question

I hope this doesn't sound too petty and cause opinionated answers.

On my page I've already queried my database for some values with counts and stored them in an array for formatting later on my page. The topicCount is a count of articles posted in each topic.

topicName     | topicCount
--------------|-------------
Latest News   | 101
Events        | 273
...

I've now decided to display a count for all articles. Now, this is what I was going to do:

SELECT COUNT(*) AS articleCount FROM blogArticles
$articleCount = $row['articleCount'];

But I'm now curious whether running a count on my array would be quicker and/or less expensive, seeming as I've already got the data in my script.

$articleCount = 0;
foreach ($results as $key=>$value) {
    $articleCount + $value['topicCount'];
}

Which method is better and why or is there too little difference to care? Is reducing 20,000 SQL requests per day to get a count much of a save?

If I was just counting array items count($array) I'd probably take a guess that counting the array was much quicker/less expensive than querying the database - but having to add up totals makes me think there could be a difference, especially if I had 1000 topics with counts.

Mark Baker · Accepted Answer · 2013-12-28 14:34:37Z

1

Almost invariably, a second database call will be more of an overhead than doing it in PHP; though you could possibly modify your original MySQL to return a grand total as well as individual topic totals. e.g. using WITH ROLLUP

SELECT topicName, 
       COUNT(topicID) AS topicCount
  FROM topicTable 
 GROUP BY topicName 
  WITH ROLLUP

should give something like:

topicName     | topicCount
--------------|-------------
Latest News   | 101
Events        | 273
...
NULL          | 374

For PHP >= 5.5.0, you can improve your foreach loop by using array_sum() and array_column() instead:

$articleCount = array_sum(
    array_column(
        $results,
        'topicCount'
    )
);

edited Dec 28, 2013 at 14:34

answered Dec 28, 2013 at 14:04

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Alma Do Over a year ago

Hm.. not sure if this is "improvement". In terms of code readability - it is, but in terms of memory - I doubt

Mark Baker Over a year ago

In terms of memory, it would come down to how many different topic categories there were in the list, but it should be marginally faster than iterating via a foreach() as well

Alma Do Over a year ago

Yes, and I don't argue with that. Just added note because whole concern is about "performance" (even if OP didn't mentioned which performance he wants to improve)

TheCarver Over a year ago

Hi @MarkBaker. Thanks for replying. I have PHP 5.3.24 so cannot use array_column(). After reading about this function, is possible for me to use array_sum() and array_map together? Will this have a similar outcome/performance to your suggestion?

Mark Baker Over a year ago

Using array_reduce() would probably be easier than array_sum() and array_map(); but the best performant option would be to do this in your SQL query

|

Collectives™ on Stack Overflow

Performance difference between DB count and Array count

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related