Re: [PHP-DB] Query Optimizing on sum() function
Addressed to: "Nomor Satu Bajingan" <[EMAIL PROTECTED]> [EMAIL PROTECTED] ** Reply to note from "Nomor Satu Bajingan" <[EMAIL PROTECTED]> Thu, 10 Jan 2002 13:51:06 + > > 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 | -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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Query Optimizing on sum() function
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 | -00-00 00:00:00 | | | hostname | varchar(120) | | | | | | remoteip | varchar(15) | | | | | | username | varchar(20) | | MUL | | | | camp_id | varchar(8) | | MUL | | | | browser_os | varchar(100) | | | | | | impressions | int(11) | | | 0 | | | cookiestring | varchar(128) | | MUL | | | | status | char(1) | | | N | | +--+--+--+-+-++ 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: select sum(impressions) impr from imp_log where camp_id='17'; but it took 5 minutes for me to do the query... but if I use count(impressions) it only tooks under 10 sec mysql> select count(impressions) from imp_log; ++ | count(impressions) | ++ |2461566 | ++ 1 row in set (0.06 sec) How to optimize the query ? the strange things is MySQL only uses a little of cpu time when I did sum() functions...Can I force MySQL to use all the cpu time ? I uses Linux Mandrake 7.0 with MySQL 4.0.0 alpha-max-log here is the MySQL variables... mysql> show variables; +-+---+ | Variable_name | Value | +-+---+ | back_log| 50 | | basedir | / | | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 32768 | | bdb_home| /var/lib/mysql/ | | bdb_max_lock| 1 | | bdb_logdir | | | bdb_shared_data | OFF
