Hi,
We have 4 tables in which we have approximately 40 Million records
per month. We are having trouble getting results from MySql as it takes
about 4-5 hours to complete for each query. We are using this primarily
for reporting purposes.
My table schema is as follows
SMAS Table:
Column Name
Type
Key
adnetwork
adnetworkResponse
campaignName
clientRequest
logkey
loggingTime
logmodule
pageName
propertyName
requestId
requestSystemId
serverResponse
sessionId
siteName
sucess
systemId
varchar(3999)
text
varchar(3999)
varchar(3999)
varchar(3999)
timestamp
varchar(3999)
varchar(3999)
varchar(3999)
varchar(3999)
varchar(3999)
text
varchar(256)
varchar(3999)
int(11)
varchar(3999)
MUL
MUL
Currently, SMAS table has 40Million records and our query takes 5 hours
to execute.
My my.cnf file is as follows and all tables are InnoDB.
[mysqld]
datadir=/mnt/data-store/mysql/data
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
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_data_home_dir=/mnt/data-store/mysql/data
innodb_data_file_path=ibdata1:15G:autoextend
innodb_buffer_pool_size=3G
max_connections=200
tmpdir=/mnt/data-store/tmp
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
My query is as follows on SMAS table
select campaignName, siteName, adnetwork,date_format(loggingTime
,'%d/%m/%Y') logDate, count(distinct requestid) adpages
from SMAS where sucess = 1 GROUP BY 1,2,3,4;
Any help will be highly appreciated.
-Karthik
