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
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