Nuno Pereira <[EMAIL PROTECTED]> wrote on 08/08/2005 01:49:44 PM: > Sebastian wrote: > > 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. > > > > > > You can see the number of updates, deletes, selects by performing a > > SHOW STATUS LIKE 'com_'; > > This shows how many queries of each types the MySQL server got since his > last reboot. > It can help you decide the best method, and the impact of making the > extra query per insert/delete. > If you update the count each _N_ seconds, the readers will see an old > value and they could, for example, not post a reply, because they didn't > saw the value being updated. > I think that normally people prefer the first, or you could the value of > _N_ be smaller (1 minute, for example). > -- > Nuno Pereira > The problem with just counting statement executions is that it doesn't show you the actual load each statement puts on the CPU. A better stat to track would be MySQL's CPU % per minute over the course of an hour. Then, during a period of similar loading, swap methods and track the CPU % again for another hour. The better method would be the one with the lowest contribution to overall load. I don't think that MySQL has an internal statistic like this, I think the OP is going to need to look at the O/S level to collect his stats. For as little load as he has, things should still scale fairly linearly. For instance, let's say these are some initial testing results: Method - # of comments added/deleted - Avg CPU %/min A - 800 - 40 B - 400 - 20 I would count that as equivalent results (no real difference). Even though B showed only half as much processing, it only had half the load. The best comparison would result from comparing performances during periods of equivalent loading. One other very important statistic to measure is "idle time" for your disks. You definitely want the method that produces the LEAST disk activity (most idle time). That means that your disks have more time to fulfill user requests and your entire system will seem more responsive. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
