MySQL Forums
Forum List  »  Performance

Re: speed up query
Posted by: Rick James
Date: April 28, 2010 06:22AM

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; 

Options: ReplyQuote


Subject
Views
Written By
Posted
4140
April 18, 2010 06:42AM
1506
April 18, 2010 04:04PM
1587
April 18, 2010 05:40PM
1315
April 27, 2010 02:29PM
1455
April 27, 2010 07:59PM
1319
April 28, 2010 12:12AM
Re: speed up query
1575
April 28, 2010 06:22AM
1523
April 29, 2010 01:03AM
1377
April 29, 2010 02:17AM
1381
April 29, 2010 08:19AM
1385
April 29, 2010 09:18AM
1312
April 30, 2010 12:30AM
1564
April 30, 2010 07:47AM
1484
April 30, 2010 08:21AM
1398
April 30, 2010 10:08AM
1319
May 01, 2010 02:34AM
1333
May 01, 2010 09:54AM
1287
May 01, 2010 10:48AM
1415
May 01, 2010 02:20PM
1355
May 02, 2010 03:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.