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]
