Re: speed up query

[Shabbir Ahmed]

thank for ur continuous reply i m already doing so for a week now, but actually equipment is increasing day by day so that data is increasing i m trying to partition and made the following changes in my.cnf on a test server but query got slower and the query is not using the right index.





[mysqld]
innodb_file_per_table=1
innodb_use_sys_malloc=1
datadir=/var/lib/mysql
innodb_read_io_threads=4
innodb_write_io_threads=2
innodb_thread_concurrency=20
innodb_log_file_size=1024M
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
innodb_open_files=2048
innodb_buffer_pool_size=3072M
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connections=1000
[mysqld_safe]
log-error=/var/log/mysqld.log
#innodb_additional_mem_pool_size=256M

system has 16gb ram, 3 gige xion (2x2).



the foloowing query should use only single partition and the right index but ....


explain partitions select count(*) from dsl_data_1 where time between '2009-12-08' and '2009-12-08';
+----+-------------+------------+-------------------------------------------------------------+-------+---------------+-----------------------+---------+------+-------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------------------------------------------------------------+-------+---------------+-----------------------+---------+------+-------+--------------------------+
| 1 | SIMPLE | dsl_data_1 | p001,p002,p003,p004,p005,p006,p007,p008,p009,p010,p011,p012 | index | NULL | idx_dsl_data_port_id1 | 5 | NULL | 10369 | Using where; Using index |
+----+-------------+------------+-------------------------------------------------------------+-------+---------------+-----------------------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)