Slow MySQL query - please help

[Zaggs]

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?

select dt_derived.*, format(revenue/clicks,2) as rpc, format(clicks/uniques*100,2) as ctr from (select dt_parked.*, sum(dt_parked_stats.uniques) as uniques,
sum(dt_parked_stats.clicks) as clicks, sum(dt_parked_stats.revenue) as revenue, sum(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 

[r937]

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

[Zaggs]

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?

[r937]

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

[Zaggs]

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

CREATE TABLE `dt_parked_stats` (
 `id` bigint(20) NOT NULL auto_increment,
 `domain` varchar(255) NOT NULL,
 `uniques` int(11) NOT NULL default '0',
 `clicks` int(11) NOT NULL default '0',
 `revenue` decimal(9,2) NOT NULL default '0.00',
 `date` date NOT NULL,
 `status` enum('0','1') NOT NULL default '0',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=42224 DEFAULT CHARSET=latin1

CREATE TABLE `dt_parked` (
 `id` int(11) NOT NULL auto_increment,
 `owner` smallint(6) NOT NULL,
 `domain` varchar(256) NOT NULL,
 `keywords` varchar(100) character set utf8 default NULL,
 `type` enum('paid','minisite') NOT NULL default 'paid',
 `active_feeds` varchar(200) NOT NULL,
 `content_type` enum('xml','rss','customhtml') NOT NULL,
 `theme` varchar(100) NOT NULL,
 `forsale` enum('0','1') NOT NULL default '1',
 `counter` enum('0','1') NOT NULL default '0',
 `adsense` enum('0','1') NOT NULL,
 `adsense_userid` varchar(100) default NULL,
 `adsense_width` varchar(32) default NULL,
 `adsense_height` varchar(32) default NULL,
 `html_content` text,
 `page_title` varchar(100) default NULL,
 `meta_tags` text,
 `date` datetime NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=403 DEFAULT CHARSET=latin1 

[r937]

okay, you seem to have no indexes at all except those create by the PRIMARY KEYs

try this --

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

[Zaggs]

okay, you seem to have no indexes at all except those create by the PRIMARY KEYs

try this --

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

[r937]

seems like you have a duplicate domain -- try deleting one of those rows and then apply the UNIQUE index again

[Zaggs]

seems like you have a duplicate domain -- try deleting one of those rows and then apply the UNIQUE index again

Thanks, it works now :-)