Maybe you can paste back the results from an 'explain' statement...also would be helpful to see the indexes on this table. Show create table smas \G;
-----Original Message----- From: Karthik Pattabhiraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 3:30 AM To: mysql@lists.mysql.com Subject: Trouble with large data in MySql 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
