can u please show us the output of show index from <TABLE_NAME>;
On 10/14/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi Karthik, > > You have many columns in a single table (although you can). But, if you > break the below table into two tables ( Vertical partitioning). It will > help > you to run your query faster. You haven't shown join_buffer_size and > sort_buffer_size. > > Secondly, check your all the queries with explain. > > On Wed, Oct 8, 2008 at 4:00 PM, Karthik Pattabhiraman < > [EMAIL PROTECTED]> wrote: > > > 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 > > > > > > > > > > > > > > > -- > Krishna Chandra Prajapati > MySQL DBA, > Ed Ventures e-Learning Pvt.Ltd. > 1-8-303/48/15, Sindhi Colony > P.G.Road, Secunderabad. > Pin Code: 500003 > Office Number: 040-66489771 > Mob: 9912924044 > URL: ed-ventures-online.com > Email-id: [EMAIL PROTECTED] >
