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,