2

Is it excpetable, or normal to wait 3 hours, 50 minutes and 39.70 seconds to run the below query?

CREATE TABLE ukbm001marketing.CampaignHistory_v2
                (
                AddressId int,                          
                CampaignId int,                         
                CampaignTypeId int,                      
                OpenUserId nvarchar(255),
                OpenDate datetime,
                CloseUserId nvarchar(255),
                CloseDate datetime
                );

INSERT INTO ukbm001marketing.CampaignHistory_v2

SELECT a.ContactId,
       b.CampaignCodeId,
       c.CampaignId,
       'mballinger',
       now(),
       NULL,
       NULL -- SELECT *
FROM 
    ukbm001marketing.temp_ContactHistory_grtthn2009_raw a
LEFT JOIN 
    ukbm001marketing.temp_CampaignCode_raw b ON a.CampaignCode = b.CampaignCode
                                             AND a.ContactDate = b.ContactDate
                                             AND a.Load_Date = b.Load_Date
LEFT JOIN 
    ukbm001marketing.temp_ContactCodes_raw c ON a.ContactCode = c.ContactCode;

Tables used in the query:

  • ukbm001marketing.temp_ContactHistory_grtthn2009_raw has 565,832 rows
  • ukbm001marketing.temp_CampaignCode_raw has 9505 rows
  • ukbm001marketing.temp_ContactCodes_raw has 39 rows

I ran this query in the command prompt line.

In the past I have worked with Microsoft SQL Server (set up by my IS Department). I am working on a project of my own and I have the following set up:

Installed Using EasyPHP12.1

  • Apache/2.4.2 (Win32) PHP/5.4.6
  • Software version: 5.5.27-log - MySQL Community Server (GPL)

Laptop Spec

  • Processor: Intel(R) Core(TM) i7-2620M CPU @ 2.70ghz 2.70ghz
  • RAM: 8.00 GB (7.88 GB usabale)
  • System 64bit Windows System

The system was running at 50% CPU usage.

I have not indexed any of the tables. I have not given the tables any primary keys. Does this issue relate to my system performance? Is it a database design issue? Or does it a setting on the mysql server?

Many thanks in advance for your help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1077250
  • 155
  • 2
  • 14
  • +1, for the question. – Shankar Narayana Damodaran May 01 '13 at 10:15
  • Run `explain` to see where the bottleneck is – juergen d May 01 '13 at 10:16
  • Indexing the appropriate columns will vastly increase performance - why have you chosen not to do this? – John Parker May 01 '13 at 10:19
  • If the tables do not have PRIMARY KEYs then they're not really tables, and there's not really any point in using MySQL for this. If (campaigncode,contactdate,load_date) is an acceptable PK then fine, otherwise the further addition of this compound key to both table a and table b will exponentially increase the speed of this query from hours to (at a guess) fractions of a second. – Strawberry May 01 '13 at 10:37
  • Thanks for the comments, Primary keys added and further research done on their importance. – user1077250 May 01 '13 at 22:16

1 Answers1

1

This is definitely an index issue. You are joining a 565.832 rows counting table on 3 fields to a 9505 table without using any indexes. This will give you a full table scan on both tables, meaning that the server will indeed have to get all of those 565832 from disk (-> slooow) and match them in memory.

Given the limited info you gave, I don't think you have done any MySQL optimalisation meaning that your join_buffer_size will be rather small. This will result in even more table scans, as MySQL will not be able to store everything in the buffer. So your 8Gb of ram will not do you any good if you do not instruct MySQL to actually use it.

So basically, create a multi-colum index on the campaigncode, contactdate and load_date fields on both the 'a' and 'b' table, and add an index on the contactcode field of the 'a' and 'c' table.

Depending on the table structure (definitely when the b or c table contain a lot of other fields that are not listed here), you might even considder adding the campaigncodeid field to the index on the 'b' table and the campaignid to the index on the 'c' table. This way, MySQL will be able to use the index to retrieve all the data, and will not need to access the actual data table to retrieve the two fields. Obviously, the penalty is that your index will be larger. Combined with some MySQL tuning, you could keep the indexes in memory, speeding the whole thing up even more.

Tom Cannaerts
  • 628
  • 1
  • 5
  • 14
  • Indexing as advised increased the speed of the join ... from 3 hours to about 3 minutes! I haven't looked at the join_buffer_size... but I am going to read into this. Thanks for your help! – user1077250 May 01 '13 at 22:18
  • join_buffer_size is only used when joining tables without using indexes (what you have fixed by now). If you can spare the RAM, you will want try increase the innodb_buffer_pool_size, preferably to a value larger than the size of your database. This will allow MySQL to load the entire database in memory (obviously assuming you are using InnoDB, but since that's default in 5.5 you probably are). – Tom Cannaerts May 02 '13 at 23:02