Hi Martin, Sorry, I am not able to understand this. Can you explain in detail please?
-Karthik From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2008 6:56 PM To: Krishna Chandra Prajapati; Karthik Pattabhiraman Cc: mysql@lists.mysql.com Subject: RE: Trouble with large data in MySql You mentioned creating an Index on any/all columns participating in a join you will also need to create an Index on the FUNCTION(column) otherwise the DB sees the function call and which implements FTS on column Warm Regards Martin ______________________________________________ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Wed, 15 Oct 2008 17:09:42 +0530 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: Re: Trouble with large data in MySql > CC: mysql@lists.mysql.com > > Hi, > > 1. Since requestid is a primary key then why you are using distinct in your > query. It's not required. > > 2. Index needs to be created on logtime. > > 3. Explain will best describe where indexing is required. > 4. In your query use group by a.logtime (date_format is not required) > > Your system must have more rams and high processor (quad core). > > This table has about 200Million records and it takes hours to come out. In > this table requestid is unique. What other indexes can I create to speed up > the response time of my query? What should I configure in mysql? Can you > help me with this? > > > > I have 6GB RAM, 2 cores and 7200 RPM disk with 1TB size. > > > On Wed, Oct 15, 2008 at 4:21 PM, Karthik Pattabhiraman < > [EMAIL PROTECTED]> wrote: > > > Hi Krishna, > > > > I have one more question for you. My table schema is as follows > > > > create table RequestDO ( > > > > country VARCHAR(256), > > > > device VARCHAR(256), > > > > devicemanufacturer VARCHAR(256), > > > > entryPage INTEGER, > > > > exitPage INTEGER, > > > > logicalPageName VARCHAR(3999), > > > > logtime TIMESTAMP, > > > > newrepeat VARCHAR(2), > > > > operator VARCHAR(256), > > > > pagename VARCHAR(3999), > > > > referer VARCHAR(256), > > > > requestid VARCHAR(256) [primary key], > > > > requesttype INTEGER, > > > > responseduration INTEGER, > > > > responsesize INTEGER, > > > > revenue INTEGER, > > > > sessionid VARCHAR(256), > > > > sitename VARCHAR(3999), > > > > source VARCHAR(3999), > > > > subscriberid VARCHAR(256), > > > > subscribermsisdn VARCHAR(256), > > > > subscribertype VARCHAR(2) > > > > ) engine=innodb; > > > > My current query is as follows: > > > > > > > > SELECT DATE_FORMAT(A.LOGTIME, '%Y-%m-%d %H') AS LOGTIME, > > > > COUNT(DISTINCT A.REQUESTID) AS PAGE_VIEWS > > > > FROM RequestDO A > > > > WHERE A.LOGTIME BETWEEN DATE_FORMAT('2008-09-15 00', '%Y-%m-%d %H') AND > > > > DATE_FORMAT('2008-09-15 23', '%Y-%m-%d %H') > > > > GROUP BY DATE_FORMAT(A.LOGTIME, '%Y-%m-%d %H') > > > > > > > > This table has about 200Million records and it takes hours to come out. In > > this table requestid is unique. What other indexes can I create to speed up > > the response time of my query? What should I configure in mysql? Can you > > help me with this? > > > > > > > > I have 6GB RAM, 2 cores and 7200 RPM disk with 1TB size. > > > > > > > > Thanks in advance > > > > Karthik > > > > > > > > > > > > *From:* Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] > > *Sent:* Wednesday, October 15, 2008 3:43 PM > > > > *To:* Karthik Pattabhiraman > > *Cc:* mysql@lists.mysql.com > > *Subject:* Re: Trouble with large data in MySql > > > > > > > > Hi, > > > > It depends on your query using join and sort. Also, on how much ram you > > have. You can refer to huge_my.cnf > > You can find huge_my.cnf in the complied mysql-server binary. > > join 12 MB > > sort 8 MB > > > > On Wed, Oct 15, 2008 at 3:29 PM, Karthik Pattabhiraman < > > [EMAIL PROTECTED]> wrote: > > > > I have not specified any values for join_buffer_size and sort_buffer_size. > > What should be the optimal values for this? > > > > > > > > -Karthik > > > > > > > > *From:* Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] > > *Sent:* Tuesday, October 14, 2008 10:03 AM > > *To:* Karthik Pattabhiraman > > *Cc:* mysql@lists.mysql.com > > *Subject:* Re: Trouble with large data in MySql > > > > > > > > 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] > > > > > > > > > > -- > > 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] > > > > > > -- > 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] ________________________________ See how Windows connects the people, information, and fun that are part of your life. See Now <http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/>
