1

I am trying to write a script in MYSQL Workbench to update my database every day using stored procedure but I find it too slow compared normal query or temporary tables. If i use temporary table the time of execution : 6 second but my stored procedure 370 second.

I really need help.

      CREATE DEFINER=`datasll`@`...` PROCEDURE `product`()
      BEGIN
      EXPLAIN SELECT  
             cs.id,
             cs.fk_c,
             cs.so, 
             cs.pr,
             cs.date,
             cs.o_p,
             cs.sh_c,
             cs.x,
             LEFT(cs.x,2),
             cs.doc,
             cs.fk_op,
             cc.id_c_c,
             cc.name,
             cc.descrip,
             left(cc.n_o,3) as bon,
             cc.fk_c_br,
             cc.de,
             cc.m_mat,
             cc.color, 
             cc.fk_c_s
      FROM
            data_pro.catas cs

      LEFT OUTER JOIN
            data_pro.c_co cc ON  data_pro.cs.fk_c_c = data_pro.cc._id_cc
      WHERE
            data_pro.cc.pap = 1
      AND   data_pro.cc.stati = 'b';
      END

When i use EXPLAIN this's the result : enter image description here

Many Thanks for any help.

  • Which RDBMS are you using? – Tom V Jun 16 '15 at 8:50
  • @Tom V i use MYSQL – Sarah Jun 16 '15 at 8:53
  • The optimizer probably creates different plans, check Explain to see the differences. Btw, your LEFT join is the same as an INNER join due to the WHERE conditions, so change to INNER. If you actually need an INNER join AND those conditions to the ON . – dnoeth Jun 16 '15 at 9:45
  • @dnoeth Thanks for your answer. I used explain the same result when i use left or inner. – Sarah Jun 17 '15 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.