Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 01:19:32 PM: > 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 > > I agree that live testing is the only way to really know what works for your system. However, I still believe that even with 500 new records an hour, you will consume much less CPU and disk time with the individual updates (especially if you are using InnoDB) than with just one of your massive recount queries. Just as you said, you really need to try it both ways to see which method works best with your system and traffic loading. Please post your testing results when you get the chance. I love these kinds of db theory problems! Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
