Hello,
I am a software developer, I am using Mysql-4.1.3b-beta-nt, ODBC3.5.1 driver on windows PC for an application development. the application which i am developing is a client-server architecture based,in which we have to store data of the BSM(Base Station Manager of CDMA network). the nature of data is records containing alarms and faults occuring in the CDMA system,after storing the data we need to generate statistical reports on these data my table structure is as fallows, CREATE TABLE ind_kar_bng_robocop_bsc_0_pm_ipc_0 ( 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, KEY DateIndex (data_GenTime), KEY gan_idIndex (gan_id) , KEY bsc_idIndex (bsc_id) , KEY bts_idIndex (bts_id) , KEY bd_typeIndex (bd_type) , KEY bd_idIndex (bd_id) , KEY item_idIndex (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_bsc_0_pm_ipc_0 where bsc_id = 0 and bts_id = 255 and data_Gentime between "2004-11-22 00:00:00" and "2004-12-10 19:41:44" and item_id = 0; +----------+-----------+---------+---------+ | Sum(m0) | Avg(m1) | Max(m5) | Min(m6) | +----------+-----------+---------+---------+ | 23376896 | 1154.9079 | 0 | 0 | +----------+-----------+---------+---------+ 1 row in set (3 min 30.35 sec) this query executed when the record count in the table ind_kar_bng_robocop_bsc_0_pm_ipc_0 was 79,21,988 records mysql> select count(*) from ind_kar_bng_robocop_bsc_0_pm_ipc_0; +----------+ | count(*) | +----------+ | 7921988 | +----------+ 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_bsc_0_pm_ipc_0 where bsc_id = 0 and bts_id = 255 and data_Gentime between "2004-11-22 00:00:00" and "2004-12-10 19:41:44" and item_id = 0 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ind_kar_bng_robocop_bsc_0_pm_ipc_0 type: ref possible_keys: DateIndex,bsc_idIndex,bts_idIndex,item_idIndex key: item_idIndex key_len: 4 ref: const rows: 820535 Extra: Using where 1 row in set (0.28 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.09 sec) Please sujjest a way to reduce query response time to few seconds either by altering system variables or by any other method possible thanks in advance, waiting for the reply, Chethan. __________________________________ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]