Re: speed up query

[Rick James]

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;