Q:

Mysql query taking too much time in stored procedure

I am using a stored procedure to fetch some data, but its taking to much time my sql query is:
   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

If i will use the same without temp table then its working good:
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

A:

Lets filter first orderitem before joining other tables. 
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

