MySQL simple select query is slow
FYI
select count(*) from events; // 16.06 sec
Result: 18,629,084 rows
select count(*) from events where billing_period=201203 // 0.69 sec
Result: 254,150 rows
create index on billing_period and use statement below:
select * from events use index(billing_period) where billing_period=201203 // 945.62 sec or 15 mins not complete I can't wait.
Why? How can I do?
[mysql]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_db03-lv_root
1.8T 456G 1.3T 27% /
tmpfs 4.0G 0 4.0G 0% /dev/shm
/dev/sda1 194M 40M 145M 22% /boot
[mysql]$ top
top - 11:37:17 up 1069 days, 9:40, 2 users, load average: 0.28, 0.29, 0.27
Tasks: 116 total, 1 running, 115 sleeping, 0 stopped, 0 zombie
Cpu(s): 4.6%us, 0.2%sy, 0.0%ni, 92.7%id, 2.5%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8193552k total, 8102228k used, 91324k free, 132652k buffers
Swap: 10305528k total, 404192k used, 9901336k free, 4811688k cached
my.cnf
[client]
#
# These options apply to all client applications
#
#
# Port and the socket
#
port = 3306
#
socket = /tmp/mysqld.sock
#
#password = my_password
#
#----------------------------------------------------------------
#
[safe_mysqld]
#
# Log file
#
err-log = /database/logs/mysql.err
#
#----------------------------------------------------------------
#
[mysqld]
skip_name_resolve
#
# Server ID must be unique to allow for replication
# Our servers are named: 1 - master -or- 2 - slave
# then the server number so mw-db02 (master) would
# be 1002, while slave mw-db03 would be 2003
#
server-id = 1002
auto-increment-increment = 4
auto-increment-offset = 2
#innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
#sync_binlog = 1
sync_binlog = 0
socket = /tmp/mysqld.sock
max-connections = 300
max-allowed-packet=8M
basedir = /database/mysql
datadir = /database/data
tmpdir = /database/tmp
log-error = /database/logs/mysqld.err
pid-file = /var/run/mysqld/mysqld.pid
#log-bin=mysqld-bin
#binlog-format = row
slave_exec_mode=IDEMPOTENT
# The number of days to keep binary logs before they are automatically purged
expire_logs_days=30
relay-log=mysqld-relay-bin
#
skip-slave-start
#read-only
#
innodb-data-home-dir = /database/data
innodb-data-file-path = ibdata1:10M:autoextend
innodb-file-per-table
innodb-log-file-size = 128M # 4GB RAM
# Buffer pool size
innodb-buffer-pool-size = 2G # 4GB RAM
innodb-log-group-home-dir = /database/logs/innodb
# Character set and collation
character-set-server=utf8
#----------------------------------------------------------------
open_files_limit=4000
table_definition_cache=3000
table_open_cache=3000
query_cache_size=102400
log-queries-not-using-indexes=1