Assuming this is unchanging data (once it is written), then let's see if a "summary" table would do the job. (If it will, you might get 10x performance improvement.)
You have:
select avg(snrup) as snrup,avg(snrdown) as snrdown,
site_ip.id,dslam_region
from dsl_data,dslam_ports,site_ip,site
where site.dslam_region='itr'
and site_ip.did=site.id
and dslam_ports.dslam_id=site_ip.id
and dsl_data.port_id=dslam_ports.id
and dsl_data.time between "2010-01-01"
and "2010-02-01"
group by site.id
order by snrdown desc;
The new table to create:
CREATE TABLE Summary (
-- keys:
dy DATE NOT NULL,
`dslam_region` varchar(100) NOT NULL,
site_id INT NOT NULL,
-- subtotals:
sum_snrup DECIMAL(8, 2) NOT NULL,
sum_snrdown DECIMAL(8, 2) NOT NULL,
ct INT UNSIGNED NOT NULL,
PRIMARY KEY (dy, dslam_region, site_id)
);
Each morning, run this to collect yesterday's data:
SELECT @yesterday := DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
INSERT INTO Summary
SELECT @yesterday as dy,
s.dslam_region
s.id,
SUM(snrup),
SUM(snrdown),
COUNT(*)
from dsl_data d, dslam_ports p, site_ip i, site s
where i.did = s.id
and p.dslam_id = i.id
and d.port_id = p.id
and d.time >= @yesterday
and d.time < CURRENT_DATE()
group by s.dslam_region, s.id
Then the 'report' becomes:
select SUM(sum_snrup) / SUM(ct) as snrup,
SUM(SUM_SNRDOWN) / SUM(ct) as snrdown,
site_id,
dslam_region
from Summary
where dslam_region = 'itr'
and dy >= "2010-01-01"
and dy < DATE_ADD("2010-02-01", INTERVAL 1 MONTH)
group by site_id
order by snrdown desc;