Re: Query Optimizing on sum() function

Addressed to: "Nomor Satu Bajingan" <bajingan_nomor_satu@hotmail.com> php-db@lists.php.net ** Reply to note from "Nomor Satu Bajingan" <bajingan_nomor_satu@hotmail.com> Thu, 10 Jan 2002 13:51:06 +0000 > > Hello Friends, > I've some performance problem, when I do sum() functions on my tables it > took 5-7 minutes to return the results.. here is my story: > I've table with 2461566 rows here is my table structure: > mysql> describe imp_log; > +--------------+--------------+------+-----+---------------------+----------------+ > | Field | Type | Null | Key | Default | Extra > | > +--------------+--------------+------+-----+---------------------+----------------+ > | sno | bigint(10) | | PRI | NULL | > auto_increment | > | advt_id | varchar(20) | | | | > | > | timestamp | datetime | | MUL | 0000-00-00 00:00:00 | > | > the problem is I want to sum the impressions from advt_id number 17 (this > advt_id has 855517 records on imp_log table).. I want to sum the > impressions..here is my query: If you want to look things up by advt_id make it a key, as it is now MySQL must search thru over 24 MILLION records to find the ones belonging to 17 while it is doing the sum. As far as trying to get higher CPU use from the system, you would probably need to buy more(RAID) and/or faster disk drives since your machine is probably banging the disks as fast as it can and the CPU doesn't have much to do but wait for the disks. Try the index first. Rick Widmer Internet Marketing Specialists http://www.developersdesk.com