[MySQL] Temoprary Table

MySQL上建立暫時性的table以供查詢，因會占用記憶體，小心爆掉

//Create a temporary table in memory for a query cache , increase the query speed.

create temporary table tmpData (

        ifname            varchar(30) not null,

        inbytecnt bigint(20) not NULL,

        outbytecnt  bigint(20) not NULL

) engine=innodb;

//Fill the data into the temporary table.

insert into tmpData(ifname,   inbytecnt,outbytecnt)

SELECT a.ifname , a.inbytecnt, a.outbytecnt

FROM pmdb.ethernetif_hour a inner join ims.nms_phy_port b on a.neeir = b.neeir and a.ifname=b.port_name 

where a.timestamped > '2013-11-21 00:00:00' and b.NEEIR = 310185427221891841 limit 20000;

//Then we can reuse the temporary table until the session broken or you drop the table.

select ifname , max(inbytecnt) INMax from tmpData group by ifname;

select ifname , Min(inbytecnt) INMin from tmpData group by ifname;

select ifname , max(outbytecnt) OUTMax from tmpData group by ifname;

select ifname , Min(outbytecnt) OUTMin from tmpData group by ifname;