1. Please don't cross-post -- it just confuses us. The reply is going to php-db.
2. It seems that you are using imp_log incorrectly. Either you add a record to it for each hit, or for a camp_id value you update the impressions field. Which is it? If the former, then a count() will give you what you need, if the second, then a simple retrieval of the value stored in impressions should do the trick. 3. If the second, then camp_id should be indexed uniquely. 4. Do count() and sum() return the same values? Hope this helps - Miles Thompson At 01:51 PM 1/10/2002 +0000, 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) >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 | 10000 > > > | >| bdb_logdir | > > > | >| bdb_shared_data | OFF > > > | >| bdb_tmpdir | /data/ads/ > > > | >| bdb_version | Sleepycat Software: Berkeley DB >3.2.9a: (October 10, >2001) > > | >| binlog_cache_size | 32768 > > > | >| character_set | latin1 > > > | >| character_sets | latin1 big5 czech euc_kr gb2312 gbk >latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 >cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek >win1250 croat cp1257 latin5 | >| concurrent_insert | ON > > > | >| connect_timeout | 5 > > > | >| datadir | /var/lib/mysql/ > > > | >| delay_key_write | ON > > > | >| delayed_insert_limit | 100 > > > | >| delayed_insert_timeout | 300 > > > | >| delayed_queue_size | 1000 > > > | >| flush | OFF > > > | >| flush_time | 0 > > > | >| ft_min_word_len | 4 > > > | >| ft_max_word_len | 254 > > > | >| ft_max_word_len_for_sort | 20 > > > | >| have_bdb | YES > > > | >| have_innodb | YES > > > | >| have_isam | YES > > > | >| have_raid | NO > > > | >| have_symlink | YES > > > | >| have_openssl | NO > > > | >| init_file | > > > | >| innodb_data_file_path | ibdata1:64M > > > | >| innodb_data_home_dir | > > > | >| innodb_flush_log_at_trx_commit | OFF > > > | >| innodb_log_arch_dir | > > > | >| innodb_log_archive | OFF > > > | >| innodb_log_group_home_dir | /var/lib/mysql/ > > > | >| innodb_flush_method | > > > | >| interactive_timeout | 28800 > > > | >| join_buffer_size | 131072 > > > | >| key_buffer_size | 16773120 > > > | >| language | /usr/share/mysql/english/ > > > | >| large_files_support | ON > > > | >| locked_in_memory | OFF > > > | >| log | ON > > > | >| log_update | OFF > > > | >| log_bin | OFF > > > | >| log_slave_updates | OFF > > > | >| log_long_queries | OFF > > > | >| long_query_time | 10 > > > | >| low_priority_updates | OFF > > > | >| lower_case_table_names | 0 > > > | >| max_allowed_packet | 1047552 > > > | >| max_binlog_cache_size | 4294967295 > > > | >| max_binlog_size | 1073741824 > > > | >| max_connections | 100 > > > | >| max_connect_errors | 10 > > > | >| max_delayed_threads | 20 > > > | >| max_heap_table_size | 16777216 > > > | >| max_join_size | 4294967295 > > > | >| max_sort_length | 1024 > > > | >| max_user_connections | 0 > > > | >| max_tmp_tables | 32 > > > | >| max_write_lock_count | 4294967295 > > > | >| myisam_bulk_insert_tree_size | 8388608 > > > | >| myisam_max_extra_sort_file_size | 256 > > > | >| myisam_max_sort_file_size | 2047 > > > | >| myisam_recover_options | OFF > > > | >| myisam_sort_buffer_size | 8388608 > > > | >| net_buffer_length | 16384 > > > | >| net_read_timeout | 30 > > > | >| net_retry_count | 10 > > > | >| net_write_timeout | 60 > > > | >| open_files_limit | 0 > > > | >| pid_file | /var/lib/mysql/hercules.pid > > > | >| port | 3306 > > > | >| protocol_version | 10 > > > | >| record_buffer | 131072 > > > | >| record_rnd_buffer | 131072 > > > | >| rpl_recovery_rank | 0 > > > | >| query_buffer_size | 0 > > > | >| safe_show_database | OFF > > > | >| server_id | 0 > > > | >| slave_net_timeout | 3600 > > > | >| skip_locking | ON > > > | >| skip_networking | OFF > > > | >| skip_show_database | OFF > > > | >| slow_launch_time | 2 > > > | >| socket | /var/lib/mysql/mysql.sock > > > | >| sort_buffer | 2097144 > > > | >| sql_mode | 0 > > > | >| table_cache | 64 > > > | >| table_type | MYISAM > > > | >| thread_cache_size | 0 > > > | >| thread_stack | 131072 > > > | >| transaction_isolation | READ-COMMITTED > > > | >| timezone | JAVT > > > | >| tmp_table_size | 33554432 > > > | >| tmpdir | /data/ads/ > > > | >| version | 4.0.0-alpha-Max-log > > > | >| wait_timeout | 28800 > > > | >+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >102 rows in set (0.03 sec) > >and here is the extended-status: >+--------------------------+----------+ >| Variable_name | Value | >+--------------------------+----------+ >| Aborted_clients | 10 | >| Aborted_connects | 3 | >| Bytes_received | 82124 | >| Bytes_sent | 5290836 | >| Connections | 60 | >| Created_tmp_disk_tables | 0 | >| Created_tmp_tables | 115 | >| Created_tmp_files | 0 | >| Delayed_insert_threads | 0 | >| Delayed_writes | 0 | >| Delayed_errors | 0 | >| Flush_commands | 1 | >| Handler_delete | 62 | >| Handler_read_first | 75 | >| Handler_read_key | 375 | >| Handler_read_next | 2261 | >| Handler_read_prev | 0 | >| Handler_read_rnd | 42 | >| Handler_read_rnd_next | 20452381 | >| Handler_update | 0 | >| Handler_write | 931 | >| Key_blocks_used | 35 | >| Key_read_requests | 2311 | >| Key_reads | 35 | >| Key_write_requests | 103 | >| Key_writes | 34 | >| Max_used_connections | 11 | >| Not_flushed_key_blocks | 0 | >| Not_flushed_delayed_rows | 0 | >| Open_tables | 16 | >| Open_files | 31 | >| Open_streams | 0 | >| Opened_tables | 22 | >| Questions | 848 | >| Select_full_join | 0 | >| Select_full_range_join | 0 | >| Select_range | 70 | >| Select_range_check | 0 | >| Select_scan | 247 | >| Slave_running | OFF | >| Slave_open_temp_tables | 0 | >| Slow_launch_threads | 0 | >| Slow_queries | 8 | >| Sort_merge_passes | 0 | >| Sort_range | 0 | >| Sort_rows | 42 | >| Sort_scan | 6 | >| Table_locks_immediate | 678 | >| Table_locks_waited | 0 | >| Threads_cached | 0 | >| Threads_created | 59 | >| Threads_connected | 9 | >| Threads_running | 1 | >| Uptime | 525435 | >+--------------------------+----------+ > >Can I more optimize the query ? I already try to use indexing..but the >query still slow.. > >Any help, comments, critics would be very appreciated > >best regards, >Bajingan > > >_________________________________________________________________ >MSN Photos is the easiest way to share and print your photos: >http://photos.msn.com/support/worldwide.aspx > > >-- >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] -- 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]
