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

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

Actually, I can not do schema change! Can I not optimize the query by modifying the query?

Thanks a lot.

no, it's already optimized