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
