select @begin_date := '2009-10-01';
select @end_date := '2009-11-01';
select @proto := id from proto where name = 'tcp';
select @user := inet_aton('192.168.2.2');
select @server := inet_aton('192.168.2.10');

-- ----------------------------------------------------------


explain
select
  sum(inBytes) as trafIn,
	sum(outBytes) as trafOut
from
	traffic
where
	(date >= @begin_date && date < @end_date);



-- ----------------------------------------------------------


explain
select
	srcIp as client,
	sum(inBytes) as trafIn,
	sum(outBytes) as trafOut
from
	traffic
where
	(date >= @begin_date && date < @end_date) && pId = @proto
group by srcIp;


-- ----------------------------------------------------------


explain
select
	dstIp as server,
	sum(inBytes) as trafIn,
	sum(outBytes) as trafOut
from
	traffic
where
	(date >= @begin_date && date < @end_date) &&
	srcIp = @user && pId = @proto
group by dstIp;


-- ----------------------------------------------------------

/*
-- explain
select
	case
		when (date >= '2010-04-01 00:00:00' && date < '2010-04-01 01:00:00') then '2010-04-01 01:00:00'
		when (date >= '2010-04-01 01:00:00' && date < '2010-04-01 02:00:00') then '2010-04-01 02:00:00'
		when (date >= '2010-04-01 02:00:00' && date < '2010-04-01 03:00:00') then '2010-04-01 03:00:00'
		when (date >= '2010-04-01 03:00:00' && date < '2010-04-01 04:00:00') then '2010-04-01 04:00:00'
		when (date >= '2010-04-01 04:00:00' && date < '2010-04-01 05:00:00') then '2010-04-01 05:00:00'
		when (date >= '2010-04-01 05:00:00' && date < '2010-04-01 06:00:00') then '2010-04-01 06:00:00'
		when (date >= '2010-04-01 06:00:00' && date < '2010-04-01 07:00:00') then '2010-04-01 07:00:00'
		when (date >= '2010-04-01 07:00:00' && date < '2010-04-01 08:00:00') then '2010-04-01 08:00:00'
		when (date >= '2010-04-01 08:00:00' && date < '2010-04-01 09:00:00') then '2010-04-01 09:00:00'
		when (date >= '2010-04-01 09:00:00' && date < '2010-04-01 10:00:00') then '2010-04-01 10:00:00'
		when (date >= '2010-04-01 10:00:00' && date < '2010-04-01 11:00:00') then '2010-04-01 11:00:00'
		when (date >= '2010-04-01 11:00:00' && date < '2010-04-01 12:00:00') then '2010-04-01 12:00:00'
		when (date >= '2010-04-01 12:00:00' && date < '2010-04-01 13:00:00') then '2010-04-01 13:00:00'
		when (date >= '2010-04-01 13:00:00' && date < '2010-04-01 14:00:00') then '2010-04-01 14:00:00'
		when (date >= '2010-04-01 14:00:00' && date < '2010-04-01 15:00:00') then '2010-04-01 15:00:00'
		when (date >= '2010-04-01 15:00:00' && date < '2010-04-01 16:00:00') then '2010-04-01 16:00:00'
		when (date >= '2010-04-01 16:00:00' && date < '2010-04-01 17:00:00') then '2010-04-01 17:00:00'
		when (date >= '2010-04-01 17:00:00' && date < '2010-04-01 18:00:00') then '2010-04-01 18:00:00'
		when (date >= '2010-04-01 18:00:00' && date < '2010-04-01 19:00:00') then '2010-04-01 19:00:00'
		when (date >= '2010-04-01 19:00:00' && date < '2010-04-01 20:00:00') then '2010-04-01 20:00:00'
		when (date >= '2010-04-01 20:00:00' && date < '2010-04-01 21:00:00') then '2010-04-01 21:00:00'
		when (date >= '2010-04-01 21:00:00' && date < '2010-04-01 22:00:00') then '2010-04-01 22:00:00'
		when (date >= '2010-04-01 22:00:00' && date < '2010-04-01 23:00:00') then '2010-04-01 23:00:00'
		when (date >= '2010-04-01 23:00:00' && date < '2010-04-01 24:00:00') then '2010-04-01 24:00:00'
	end as `period`,
--	inet_ntoa(dstIp) as server,
	count(*) as count
--	sum(psize) as data
from
	traffic
where
	(srcPort = 0) &&
	(date >= @begin_date && date < @end_date)
group by period;
*/

-- ----------------------------------------------------------


explain
select
	dstPort as port,
	sum(inBytes) as trafIn,
	sum(outBytes) as trafOut
from
	traffic
where
	(date >= @begin_date && date < @end_date) &&
	srcIp = @user && dstIp = @server && pId = @proto
group by dstPort;


-- ----------------------------------------------------------

/*
explain
select
	srcIp
from
	traffic
where
	(date >= @begin_date && date < @end_date) && dstIp = @server
group by srcIp;
*/

-- ----------------------------------------------------------
