Hello, Nomor Satu Bajingan wrote: > > 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 | > | > | 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)
This is obvious, when you use count on a key field the server only accesses the respective index. If you do sum() it has to traverse the actual table. Keep in mind that in practice, the table and the indexes are separate things. Actually, if you did count(*) it would be faster than count(field) when the field may be NULL, because count(field) still has to exclude entries where field is NULL. > 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 ? If MySQL is not using the CPU all the time that means that it is the process is doing non CPU based IO, like disk access to non cached data. This may be a consequence of the fact that your table may have many fields, so to traverse it, MySQL has to do a lot of disk seeking. The solution for this may be splitting the table with the fields that you want to account into child tables. Watch out for joins with more than two tables. > Can I more optimize the query ? I already try to use indexing..but the query > still slow.. Indexes do not make miracles to queries that still have to traverse long tables. > Any help, comments, critics would be very appreciated Do you appreciate it? Database consulting is $200/hour, credit cards or paypal are accepted. Just kidding! :-) Regards, Manuel Lemos -- PHP General 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]
