title: mysql stored procedure is slower 20 times than standard query
body: <p>i have 10 tables with same structure except table name.</p>
  
  <p>i have a sp (stored procedure) defined as following:</p>
  
  <pre><code> select * from table1 where (@param1 IS NULL OR col1=@param1)
   UNION ALL
   select * from table2 where (@param1 IS NULL OR col1=@param1)
   UNION ALL
   ...
   ...
   UNION ALL
   select * from table10 where (@param1 IS NULL OR col1=@param1)
  </code></pre>
  
  <p>I am calling the sp with the following line:</p>
  
  <pre><code>call mySP('test')  //it executes in 6,836s
  </code></pre>
  
  <p>Then I opened a new standard query window. I just copied the query above. Then replaced @param1 with 'test'.</p>
  
  <p>This executed in 0,321s and is about 20 times faster than the stored procedure.</p>
  
  <p>I changed the parameter value repeatedly for preventing the result to be cached. But this did not change the result. The SP is about 20 times slower than the equivalent standard query.</p>
  
  <p>Please can you help me to figure out why this is happening ? </p>
  
  <p>Did  anybody encounter similar issues? </p>
  
  <p>I am using mySQL 5.0.51 on windows server 2008 R2 64 bit. </p>
  
  <p>edit: I am using Navicat for test.</p>
  
  <p>Any idea will be helpful for me.</p>
  
  <p>EDIT1:</p>
  
  <p>I just have done some test according to Barmar's answer.</p>
  
  <p>At finally i have changed the sp like below with one just one row:</p>
  
  <pre><code> SELECT * FROM table1 WHERE col1=@param1 AND col2=@param2
  </code></pre>
  
  <p>Then firstly i executed the standart query </p>
  
  <pre><code> SELECT * FROM table1 WHERE col1='test' AND col2='test'  //Executed in 0.020s
  </code></pre>
  
  <p>After i called the my sp:</p>
  
  <pre><code> CALL MySp('test','test')    //Executed in 0.466s
  </code></pre>
  
  <p>So i have changed where clause entirely but nothing changed. And i called the sp from mysql command window instead of navicat. It gave same result. I am still stuck on it.</p>
  
  <p>my sp ddl:</p>
  
  <pre><code> CREATE DEFINER = `myDbName`@`%`
   PROCEDURE `MySP` (param1 VARCHAR(100), param2 VARCHAR(100))
   BEGIN
      SELECT * FROM table1 WHERE col1=param1 AND col2=param2
   END
  </code></pre>
  
  <p>And col1 and col2 is combined indexed.</p>
  
  <p>You could say that why dont you use standart query then? My software design is not proper for this. I must use stored procedure. So this problem is highly important to me.</p>
  
  <p>EDIT2:</p>
  
  <p>I have gotten query profile informations. Big difference is because of "sending data row" in SP Profile Information. Sending data part takes %99 of query execution time.  I am doing test on local database server. I am not connecting from remote computer. </p>
  
  <p>SP Profile Informations
     <img src="https://i.stack.imgur.com/C16ob.png" alt="SP Profile Information"></p>
  
  <p>Query Profile Informations
    <img src="https://i.stack.imgur.com/0ZcBH.png" alt="enter image description here"></p>
  
  <p>I have tried force index statement like below in my sp. But same result.</p>
  
  <pre><code> SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=@param1 AND col2=@param2
  </code></pre>
  
  <p>I have changed sp like below.</p>
  
  <pre><code> EXPLAIN SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=param1 AND col2=param2
  </code></pre>
  
  <p>This gave this result:</p>
  
  <pre><code> id:1
   select_type=SIMPLE
   table:table1
   type=ref
   possible_keys:NULL
   key:NULL
   key_len:NULL
   ref:NULL
   rows:292004
   Extra:Using where
  </code></pre>
  
  <p>Then i have executed the query below. </p>
  
  <pre><code> EXPLAIN SELECT * FROM table1 WHERE col1='test' AND col2='test'
  </code></pre>
  
  <p>Result is:</p>
  
  <pre><code> id:1
   select_type=SIMPLE
   table:table1
   type=ref
   possible_keys:col1_co2_combined_index
   key:col1_co2_combined_index
   key_len:76
   ref:const,const
   rows:292004
   Extra:Using where
  </code></pre>
  
  <p>I am using FORCE INDEX statement in SP. But it insists on not using index. Any idea? I think i am close to end :)</p>