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]