CODEWITHSUNDEEP
phpmysqlsql
Crimsonfox
Crimsonfox

Reputation: 422

What's more efficient? A number of SQL queries or looping through them all?

Hopefully an easy one and more so to quell my interest on the matter as opposed to a proper problem.

I have a DB full of events and a time they happened at. I'm going to display how many total events happened on each day for the past five days. Now would it be better to do an SQL query for each day eg:

SELECT * FROM table WHERE time BETWEEN beginningDay1 AND endDay1
$num = num_rows
SELECT * FROM table WHERE time BETWEEN beginningDay2 AND endDay2
$num2 = num_rows

OR

SELECT * FROM table
for each row
    case day 1
         day1Count++
    case day 2
         day2count++

Just keep in mind that the amount of events that could happen on one day could be anything.

Upvotes: 0

Views: 178

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332521

PHP will never scale like SQL does to count those rows. That's data that needs to be transferred over the wire, object(s) to hold the data & the operation(s) to count the day(s) when the database is specifically optimized for operations like this:

SELECT SUM(CASE 
             WHEN t.time BETWEEN beginningDay1 AND endDay1 THEN 1
             ELSE 0
           END) AS num_day1,
       SUM(CASE 
             WHEN t.time BETWEEN beginningDay2 AND endDay2 THEN 1
             ELSE 0
           END) AS num_day2
  FROM YOUR_TABLE t

There's no looping necessary in PHP.

Upvotes: 2

aroth
aroth

Reputation: 54796

The fastest approach would be to do a single query that fetches all of the information you want and computes the counts grouped the way you want them, if possible. So something along the lines of:

SELECT day, COUNT(*) FROM table GROUP BY day;

Then in PHP you just loop the result-set and extract the counts.

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881153

It's normally faster if you leave selection, ordering and grouping to the database itself, that's what it's optimised for.

A query like:

select mydate, count(*) as quant from table
where mydate between '2011-01-01' and '2011-01-03'
group by mydate

should be sufficient. This will give you a table like:

mydate      quant
----------  -----
2011-01-01     42
2011-01-02     12
2011-01-03    197

and you just process the rows themselves.

Upvotes: 3

ysrb
ysrb

Reputation: 6740

The least DB calls the better. You can try:

SELECT (SELECT COUNT(*) FROM table WHERE time BETWEEN beginningDay1 AND endDay1) AS BDAY1,
 (SELECT COUNT(*) FROM table WHERE time BETWEEN beginningDay2 AND endDay2) AS BDAY2

Upvotes: 3

Related Questions