[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.
--
Nuno Pereira
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
