Re: [PHP-DB] SQL for counting comments - is this smart?

[Martin Zvarík <mzvarik@xxxxxxxxx>]

Chris napsal(a):
> Martin Zvarík wrote:
> > Is it smart to use all of this on one page?
> > Or should I rather do one SQL and let PHP count it?
> >
> >
> > $q = $DB->q("SELECT COUNT(*) FROM comments");
> > $int_total = $DB->frow($q);
> >
> > $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved IS NULL");
> > $int_waiting = $DB->frow($q);
> >
> > $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=0");
> > $int_deleted = $DB->frow($q);
> >
> > $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=1");
> > $int_approved = $DB->frow($q);
> >
> > $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=2");
> > $int_banned = $DB->frow($q);
>
> Each one of these probably going to scan the whole table because the 
> approved column isn't going to be selective enough to use an index.
>
> You might be better off doing:
>
> select approved, count(*) from comments group by approved;
>
> then in php separating them out:
>
> while ($row = $DB->frow($q)) {
>   switch ($row['approved']) {
>      case null:
>        $waiting = $row['count'];
>      break;
>      case 0:
>        $deleted = $row['count'];
>      break;
>      case 1:
>        $approved = $row['count'];
>      break;
>   }
> }
>
> $total = $waiting + $approved + $deleted;
Duh, of course, thanks :)

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php