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!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
