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

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

am 17.03.2009 02:21:18 von Martin Zvarik

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