Re: Query Optimizing on sum() function
| From: | Miles Thompson | Date: | Thu, 10 Jan 2002 14:52:36 +0000 |
| Subject: | Re: Query Optimizing on sum() function | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-15608@lists.php.net to get a copy of this message | ||
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: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net