SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,048
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow MySQL query - please help

    Hi Guys!

    I have a mysql query that is running very slow and is causing my page to take forever to load. The "dt_parked_stats" table has over 40,000 rows. Is there anyway to speed this query up?

    PHP Code:
    select dt_derived.*, format(revenue/clicks,2) as rpcformat(clicks/uniques*100,2) as ctr from (select dt_parked.*, sum(dt_parked_stats.uniques) as uniques,
    sum(dt_parked_stats.clicks) as clickssum(dt_parked_stats.revenue) as revenuesum(dt_parked_stats.net_revenue) as net_revenue from dt_parked left outer join 
    dt_parked_stats on dt_parked
    .domain dt_parked_stats.domain and dt_parked_stats.date between "2010/01/01" and now() group by dt_parked.domain) as 
    dt_derived order by dt_derived.domain 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    step 1: make sure the correct columns for this query are indexed
    step 2: do an EXPLAIN on the query
    step 3: go back to step 1

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,048
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    step 1: make sure the correct columns for this query are indexed
    step 2: do an EXPLAIN on the query
    step 3: go back to step 1

    Should I index all columns used in the query?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no

    for example, you wouldn't index dt_parked_stats.uniques, because all you do is SUM that column

    can you do a SHOW CREATE TABLE for both tables please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,048
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no

    for example, you wouldn't index dt_parked_stats.uniques, because all you do is SUM that column

    can you do a SHOW CREATE TABLE for both tables please
    PHP Code:
    CREATE TABLE `dt_parked_stats` (
     `
    idbigint(20NOT NULL auto_increment,
     `
    domainvarchar(255NOT NULL,
     `
    uniquesint(11NOT NULL default '0',
     `
    clicksint(11NOT NULL default '0',
     `
    revenuedecimal(9,2NOT NULL default '0.00',
     `
    datedate NOT NULL,
     `
    statusenum('0','1'NOT NULL default '0',
     
    PRIMARY KEY  (`id`)
    ENGINE=MyISAM AUTO_INCREMENT=42224 DEFAULT CHARSET=latin1

    CREATE TABLE 
    `dt_parked` (
     `
    idint(11NOT NULL auto_increment,
     `
    ownersmallint(6NOT NULL,
     `
    domainvarchar(256NOT NULL,
     `
    keywordsvarchar(100character set utf8 default NULL,
     `
    typeenum('paid','minisite'NOT NULL default 'paid',
     `
    active_feedsvarchar(200NOT NULL,
     `
    content_typeenum('xml','rss','customhtml'NOT NULL,
     `
    themevarchar(100NOT NULL,
     `
    forsaleenum('0','1'NOT NULL default '1',
     `
    counterenum('0','1'NOT NULL default '0',
     `
    adsenseenum('0','1'NOT NULL,
     `
    adsense_useridvarchar(100) default NULL,
     `
    adsense_widthvarchar(32) default NULL,
     `
    adsense_heightvarchar(32) default NULL,
     `
    html_contenttext,
     `
    page_titlevarchar(100) default NULL,
     `
    meta_tagstext,
     `
    datedatetime NOT NULL,
     
    PRIMARY KEY  (`id`)
    ENGINE=MyISAM AUTO_INCREMENT=403 DEFAULT CHARSET=latin1 

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, you seem to have no indexes at all except those create by the PRIMARY KEYs

    try this --
    Code:
    ALTER TABLE dt_parked ADD UNIQUE domain_ux (domain);
    
    ALTER TABLE dt_parked_stats ADD INDEX date_ix (`date`);
    ALTER TABLE dt_parked_stats ADD INDEX domain_ix (domain);
    and see if that improves the query speed

    if it doesn't, please do an EXPLAIN on the query for us
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,048
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, you seem to have no indexes at all except those create by the PRIMARY KEYs

    try this --
    Code:
    ALTER TABLE dt_parked ADD UNIQUE domain_ux (domain);
    
    ALTER TABLE dt_parked_stats ADD INDEX date_ix (`date`);
    ALTER TABLE dt_parked_stats ADD INDEX domain_ix (domain);
    and see if that improves the query speed

    if it doesn't, please do an EXPLAIN on the query for us
    The last two work fine. The first alter gives the following error:

    #1062 - Duplicate entry 'domain.ru' for key 2

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    seems like you have a duplicate domain -- try deleting one of those rows and then apply the UNIQUE index again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,048
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    seems like you have a duplicate domain -- try deleting one of those rows and then apply the UNIQUE index again
    Thanks, it works now :-)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •