Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore
    Posts
    5

    Unanswered: Query optimization

    Hi,

    Any help, how to optimize the last query -

    The tables on which the query is based on are as follows:
    CREATE TABLE `if_srcport` (
    `input_if` tinyint(3) unsigned default NULL,
    `output_if` tinyint(3) unsigned default NULL,
    `port` smallint(5) unsigned default NULL,
    `pkts` int(10) unsigned default NULL,
    `bytes` int(10) unsigned default NULL,
    `flows` int(10) unsigned default NULL,
    `time` datetime default NULL,
    `routerip` varchar(15) default NULL
    );


    CREATE TABLE port (
    proto TINYINT UNSIGNED NOT NULL,
    port SMALLINT UNSIGNED,
    name VARCHAR(255)
    );



    Query:

    SELECT if_srcport.srcport,sum(if_srcport.pkts) pkts,sum(if_srcport.bytes) bytes,sum(if_srcport.flows) flows,port.name
    FROM if_srcport left outer join port on if_srcport.srcport=port.port
    WHERE if_srcport.routerip='202.3.75.249' and if_srcport.input_if=1
    GROUP BY if_srcport.srcport
    ORDER BY bytes DESC LIMIT 10;

    But the problem is that this query is taking too much time.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    alter table if_srcport add index (port), add index (routerip, input_if)
    alter table port add index (port);

    p.s. your GROUP BY should be GROUP BY if_srcport.srcport, port.name

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Bangalore
    Posts
    5
    Actually, I can not do schema change! Can I not optimize the query by modifying the query?

    Thanks a lot.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sanghmitra
    Can I not optimize the query by modifying the query?
    no, it's already optimized
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •