Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005
10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar query last night, but i didnt use group
by.
I have question, it it better to use COUNT(*) ? i thought there is
extra
mysql optimization when you use *
thanks.
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005
IMHO, the better solution is to INCREMENT your comment count *as you
add each comment* . There will be much less SQL processing involved
and it keeps your database in a consistent state.
Doing things your way, you accumulate "uncounted" comments and your
count will be wrong for the period between whole table recounts. If
you update your static "comment count" statistic every time you add
or delete a comment, you won't run into this problem.
Plus, think of how often you would have to summarize (recount) your
entire table just to register a few additions/deletions. How many
processing cycles will you save by just setting the value to what it
should be at the finish of the comment transaction?
This is one of those time when a "dynamic" solution is too "heavy"
for frequent use.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
It is not a bad idea to also DECREMENT the comment count each time a
comment is deleted (if you do it), but that doesn't occur very often.
This is implicit in your comment, but is not a bad idea to explicit it.
I sort of agree with you guys but i think there are downsides to both
methods..
if i do update the comment column when comments are added / deleted then
that is an extra query each time someone posts. in an hour we can get
several hundred comments, that also means several hundred db queries.
at least i figured i can update these fields every 5 minutes or so and
save some extra calls each time someone posts a comment. i guess i have
to figure out which method would be best when dealing with a lot of
traffic, but i think it will be neglible. right now that subquery runs
in under .25 ms with 50,000 records to count.. not too bad i guess.
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
