Hi mike,
as i have mentioned in my previous mail
the table structure which i am using is as fallows,
CREATE TABLE IND_KAR_BNG_Metallica_PS_RT_4
(
gan_id INTEGER NOT NULL,
bsc_id INTEGER NOT NULL,
bts_id INTEGER NOT NULL,
bd_type VARCHAR(10) NOT NULL,
bd_id INTEGER NOT NULL,
duplex VARCHAR(10) NOT NULL,
data_GenTime DATETIME NOT NULL,
item_id INTEGER NOT NULL,
M0 INTEGER NOT NULL,
M1 INTEGER NOT NULL,
M2 INTEGER NOT NULL,
M3 INTEGER NOT NULL,
M4 INTEGER NOT NULL,
M5 INTEGER NOT NULL,
M6 INTEGER NOT NULL,
M7 INTEGER NOT NULL,
M8 INTEGER NOT NULL,
M9 INTEGER NOT NULL,
M10 INTEGER NOT NULL,
M11 INTEGER NOT NULL,
M12 INTEGER NOT NULL,
M13 INTEGER NOT NULL,
M14 INTEGER NOT NULL,
M15 INTEGER NOT NULL,
M16 INTEGER NOT NULL,
M17 INTEGER NOT NULL,
M18 INTEGER NOT NULL,
M19 INTEGER NOT NULL,
M20 INTEGER NOT NULL,
M21 INTEGER NOT NULL,
M22 INTEGER NOT NULL,
M23 INTEGER NOT NULL,
M24 INTEGER NOT NULL,
M25 INTEGER NOT NULL,
M26 INTEGER NOT NULL,
M27 INTEGER NOT NULL,
M28 INTEGER NOT NULL,
M29 INTEGER NOT NULL,
M30 INTEGER NOT NULL,
M31 INTEGER NOT NULL,
INDEX RetreiveIndex (data_GenTime,gan_id ,bsc_id
,bts_id ,bd_type ,bd_id ,item_id));
the type of query that is executed is as below
mysql> select Sum(m0),Avg(m1),Max(m5),Min(m6) from
ind_kar_bng_robocop_gan_0_pm_ipc_0 where
bsc_id = 255 and data_Gentime between "2004-11-09
00:00:00" and "2004-11-10 19:41:44"
and item_id = 0;
+---------+----------+---------+---------+
| Sum(m0) | Avg(m1) | Max(m5) | Min(m6) |
+---------+----------+---------+---------+
| 3899200 | 256.0000 | 0 | 0 |
+---------+----------+---------+---------+
1 row in set (5.67 sec)
this query executed when he record count in the table
ind_kar_bng_robocop_gan_0_pm_ipc_0 was 1096650 records
as the number of record in the table keep on growing
the
query response time increases..
the explain select result of the same query is here
mysql> explain select Sum(m0),Avg(m1),Max(m5),Min(m6)
from ind_kar_bng_robocop_gan_0_pm
_ipc_0 where
-> bsc_id = 255 and data_Gentime between
"2004-11-09 00:00:00" and "2004-11-10 19:
41:44"
-> and item_id = 0 \G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: ind_kar_bng_robocop_gan_0_pm_ipc_0
type: ALL
possible_keys: RetreiveIndex
key: NULL
key_len: NULL
ref: NULL
rows: 1096650
Extra: Using where
1 row in set (0.00 sec)
these are my system variables.
mysql> show variables;
+---------------------------------+-----------------------------------+
| Variable_name | Value
|
+---------------------------------+-----------------------------------+
| back_log | 50
|
| basedir | C:\mysql\
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 8388608
|
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results | latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir |
C:\mysql\share\charsets/ |
| collation_connection | latin1_swedish_ci
|
| collation_database | latin1_swedish_ci
|
| collation_server | latin1_swedish_ci
|
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | C:\mysql\data\
|
| date_format | %Y-%m-%d
|
| datetime_format | %Y-%m-%d %H:%i:%s
|
| default_week_format | 0
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| expire_logs_days | 0
|
| flush | OFF
|
| flush_time | 1800
|
| ft_boolean_syntax | + -><()~*:""&|
|
| ft_max_word_len | 84
|
| ft_min_word_len | 4
|
| ft_query_expansion_limit | 20
|
| ft_stopword_file | (built-in)
|
| group_concat_max_len | 1024
|
| have_archive | NO
|
| have_bdb | NO
|
| have_compress | YES
|
| have_crypt | NO
|
| have_innodb | YES
|
| have_isam | NO
|
| have_geometry | YES
|
| have_ndbcluster | NO
|
| have_openssl | NO
|
| have_query_cache | YES
|
| have_raid | NO
|
| have_rtree_keys | YES
|
| have_symlink | YES
|
| init_connect |
|
| init_file |
|
| init_slave |
|
| innodb_additional_mem_pool_size | 1048576
|
| innodb_buffer_pool_awe_mem_mb | 0
|
| innodb_buffer_pool_size | 8388608
|
| innodb_data_file_path |
ibdata1:10M:autoextend |
| innodb_data_home_dir |
|
| innodb_fast_shutdown | ON
|
| innodb_file_io_threads | 4
|
| innodb_file_per_table | OFF
|
| innodb_flush_log_at_trx_commit | 1
|
| innodb_flush_method |
|
| innodb_force_recovery | 0
|
| innodb_lock_wait_timeout | 50
|
| innodb_log_arch_dir |
|
| innodb_log_archive | OFF
|
| innodb_log_buffer_size | 1048576
|
| innodb_log_file_size | 5242880
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | .\
|
| innodb_max_dirty_pages_pct | 90
|
| innodb_mirrored_log_groups | 1
|
| innodb_open_files | 300
|
| innodb_thread_concurrency | 8
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 8388600
|
| key_cache_age_threshold | 300
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| language |
C:\mysql\share\english\ |
| large_files_support | ON
|
| license | GPL
|
| local_infile | ON
|
| log | OFF
|
| log_bin | OFF
|
| log_error |
.\user-ce3b197d1c.err |
| log_slave_updates | OFF
|
| log_slow_queries | OFF
|
| log_update | OFF
|
| log_warnings | 1
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_file_system | OFF
|
| lower_case_table_names | 1
|
| max_allowed_packet | 1048576
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connect_errors | 10
|
| max_connections | 100
|
| max_delayed_threads | 20
|
| max_error_count | 64
|
| max_heap_table_size | 16777216
|
| max_insert_delayed_threads | 20
|
| max_join_size | 4294967295
|
| max_length_for_sort_data | 1024
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 4294967295
|
| max_sort_length | 1024
|
| max_tmp_tables | 32
|
| max_user_connections | 0
|
| max_write_lock_count | 4294967295
|
| myisam_data_pointer_size | 4
|
| myisam_max_extra_sort_file_size | 2147483648
|
| myisam_max_sort_file_size | 2147483647
|
| myisam_recover_options | OFF
|
| myisam_repair_threads | 1
|
| myisam_sort_buffer_size | 8388608
|
| named_pipe | OFF
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| old_passwords | OFF
|
| open_files_limit | 510
|
| pid_file |
C:\mysql\data\user-ce3b197d1c.pid |
| port | 3306
|
| preload_buffer_size | 32768
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 0
|
| query_cache_type | ON
|
| query_prealloc_size | 8192
|
| range_alloc_block_size | 2048
|
| read_buffer_size | 131072
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
|
| relay_log_purge | ON
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| shared_memory | OFF
|
| shared_memory_base_name | MYSQL
|
| server_id | 0
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slave_net_timeout | 3600
|
| slow_launch_time | 2
|
| sort_buffer_size | 2097144
|
| sql_mode |
|
| storage_engine | MyISAM
|
| sync_binlog | 0
|
| sync_frm | ON
|
| table_cache | 64
|
| table_type | MyISAM
|
| thread_cache_size | 0
|
| thread_stack | 196608
|
| time_format | %H:%i:%s
|
| system_time_zone | Korea Standard
Time |
| time_zone | SYSTEM
|
| tmp_table_size | 33554432
|
| tmpdir |
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| version | 4.1.3b-beta-nt
|
| version_comment | Source
distribution |
| version_compile_machine | i32
|
| version_compile_os | NT
|
| wait_timeout | 28800
|
+---------------------------------+-----------------------------------+
170 rows in set (0.02 sec)
Please sujjest a way to improve query response time
either by altering system variables or by any other
method possible
thanks in advance,
waiting for the reply,
Chethan.
__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com
