我正在使用存储过程来获取一些数据,但是我的sql查询需要花费很多时间:
CREATE DEFINER=``@`%` PROCEDURE `test_sp`(in _orderIds varchar(255))
BEGIN
drop temporary table if exists temp;
create temporary table temp( val int );
set @sql = concat("insert into temp (val) values ('",
replace((_orderIds), ",", "'),('"),"');");
prepare stmt1 from @sql;
execute stmt1;
SELECT p.productid,p.HSNNo,oi.orderid,cast(IFNULL(oi.dispatched,0) as
decimal(10,2))
FROM orderitem oi
inner JOIN producttax pt on pt.ProductId=oi.productid
inner JOIN product p on p.ProductId=pt.ProductId
WHERE oi.orderid in(select val from temp)
GROUP BY oi.productid,p.HSNNo,oi.orderid,oi.orderitemid;
END
如果我在没有临时表的情况下使用相同的表,那么它工作得很好:
CREATE DEFINER=``@`%` PROCEDURE `test_sp`(in _orderIds varchar(255))
BEGIN
drop temporary table if exists temp;
create temporary table temp( val int );
set @sql = concat("insert into temp (val) values ('",
replace((_orderIds), ",", "'),('"),"');");
prepare stmt1 from @sql;
execute stmt1;
SELECT p.productid,p.HSNNo,oi.orderid,cast(IFNULL(oi.dispatched,0) as
decimal(10,2))
FROM orderitem oi
inner JOIN producttax pt on pt.ProductId=oi.productid
inner JOIN product p on p.ProductId=pt.ProductId
WHERE oi.orderid in(5465,7687,876)
GROUP BY oi.productid,p.HSNNo,oi.orderid,oi.orderitemid;
END
让我们先过滤orderitem
,然后再连接其他表。
create DEFINER=``@`%` procedure `test_sp`(in _orderIds varchar(255))
begin
drop temporary table if exists temp;
create temporary table temp(val int );
set @sql = concat("insert into temp (val) values ('",
replace((_orderIds), ",", "'),('"),"');");
prepare stmt1 from @sql;
execute stmt1;
select p.productid,p.HSNNo,oi.orderid,cast(IFNULL(oi.dispatched,0) as
decimal(10,2))
from
(select orderid, dispatched, productid from orderitem where orderid in (select val from temp)) oi
inner join producttax pt on pt.ProductId=oi.productid
inner join product p on p.ProductId=pt.ProductId
group by oi.productid,p.HSNNo,oi.orderid,oi.orderitemid;
end