querying huge database table takes too much of time in mysql
- by Vijay
Hi all, I am running sql queries on a mysql db table that has 110Mn+ unique records for whole day.
Problem: Whenever I run any query with "where" clause it takes at least 30-40 mins. Since I want to generate most of data on the next day, I need access to whole db table.
Could you please guide me to optimize / restructure the deployment model?
Site description:
mysql Ver 14.12 Distrib 5.0.24, for pc-linux-gnu (i686) using readline 5.0
4 GB RAM,
Dual Core dual CPU 3GHz
RHEL 3
my.cnf contents :
[root@reports root]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql/data/
socket=/tmp/mysql.sock
sort_buffer_size = 2000000
table_cache = 1024
key_buffer = 128M
myisam_sort_buffer_size = 64M
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysql.server]
user=mysql
basedir=/data/mysql/data/
[mysqld_safe]
err-log=/data/mysql/data/mysqld.log
pid-file=/data/mysql/data/mysqld.pid
[root@reports root]#
DB table details:
CREATE TABLE `RAW_LOG_20100504` (
`DT` date default NULL,
`GATEWAY` varchar(15) default NULL,
`USER` bigint(12) default NULL,
`CACHE` varchar(12) default NULL,
`TIMESTAMP` varchar(30) default NULL,
`URL` varchar(60) default NULL,
`VERSION` varchar(6) default NULL,
`PROTOCOL` varchar(6) default NULL,
`WEB_STATUS` int(5) default NULL,
`BYTES_RETURNED` int(10) default NULL,
`RTT` int(5) default NULL,
`UA` varchar(100) default NULL,
`REQ_SIZE` int(6) default NULL,
`CONTENT_TYPE` varchar(50) default NULL,
`CUST_TYPE` int(1) default NULL,
`DEL_STATUS_DEVICE` int(1) default NULL,
`IP` varchar(16) default NULL,
`CP_FLAG` int(1) default NULL,
`USER_LOCATE` bigint(15) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=200000000;
Thanks in advance!
Regards,
