存储过程优化就学习

存储过程优化求学习

有操作到的共有4个表，一个表的数据有百万条以上，每次统计都是没有反应

以前没有写过存储过程，写了这个，但是效率超级慢，反应不过来，小的统计的时候可以返回数据，大的时候不返回了

表结构如下：

SQL code

  
CREATE TABLE `tbC` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL,
  `sType` varchar(50) NOT NULL,
  `createtime` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbB` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(10) unsigned default NULL,
  `username` varchar(50) default NULL,
  `password` varchar(50) default NULL,
  `sGroup` varchar(50) default NULL,
  `sCet` varchar(50) default NULL,
  `sType` varchar(50) default '',
  `realname` varchar(50) default NULL,
  `detail` varchar(500) default NULL,
  `dtlasttime` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbA` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(10) unsigned default NULL,
  `username` varchar(50) default NULL,
  `dtltime` datetime default NULL,
  `sType` varchar(50) NOT NULL,
  `Prices` decimal(10,0) NOT NULL,
  `sTime` varchar(50) NOT NULL,
  `createtime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbD` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL,
  `datetime1` datetime NOT NULL,
  `datetime2` datetime NOT NULL,
  `createtime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

存储过程：

SQL code

  
CREATE PROCEDURE `get_rpb` (pid int,sid int)
BEGIN

    DECLARE dtime1,dtime2 datetime;

    SELECT `datetime1`,`datetime2` into dtime2,dtime1 FROM `tbD` WHERE (`pid` = pid) AND (`id`=sid);

    SELECT sType,sGroup,sCet,SUM(sTime) AS total,SUM(Prices) AS totalprice FROM (
        SELECT 
            a.*,b.sGroup,B.sCet 
        FROM 
            tbA a,tbB b,tbC c
        WHERE
            (a.pid=b.pid) AND (a.pid=c.pid) AND (a.pid=pid) AND (a.username=b.username) AND (a.sType=c.sType)
    ) AS ABC WHERE (`createtime` between dtime1 and dtime2) GROUP BY sType,sGroup,sCet;
END;

统计是调用类似：call get_rpb(31,3355);

求各位帮忙下，谢谢了!

表a上加pid索引 username索引 atype索引 createtime索引

b上加pid索引 username索引

c上加stype索引