mysql stored procedure is slower 20 times than standard query

Question

i have 10 tables with same structure except table name.

i have a sp (stored procedure) defined as following:

 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)

I am calling the sp with the following line:

call mySP('test')  //it executes in 6,836s

Then I opened a new standard query window. I just copied the query above. Then replaced @param1 with 'test'.

This executed in 0,321s and is about 20 times faster than the stored procedure.

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.

Please can you help me to figure out why this is happening ?

Did anybody encounter similar issues?

I am using mySQL 5.0.51 on windows server 2008 R2 64 bit.

edit: I am using Navicat for test.

Any idea will be helpful for me.

EDIT1:

I just have done some test according to Barmar's answer.

At finally i have changed the sp like below with one just one row:

 SELECT * FROM table1 WHERE col1=@param1 AND col2=@param2

Then firstly i executed the standart query

 SELECT * FROM table1 WHERE col1='test' AND col2='test'  //Executed in 0.020s

After i called the my sp:

 CALL MySp('test','test')    //Executed in 0.466s

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.

my sp ddl:

 CREATE DEFINER = `myDbName`@`%`
 PROCEDURE `MySP` (param1 VARCHAR(100), param2 VARCHAR(100))
 BEGIN
    SELECT * FROM table1 WHERE col1=param1 AND col2=param2
 END

And col1 and col2 is combined indexed.

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.

EDIT2:

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.

SP Profile Informations

Query Profile Informations

I have tried force index statement like below in my sp. But same result.

 SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=@param1 AND col2=@param2

I have changed sp like below.

 EXPLAIN SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=param1 AND col2=param2

This gave this result:

 id:1
 select_type=SIMPLE
 table:table1
 type=ref
 possible_keys:NULL
 key:NULL
 key_len:NULL
 ref:NULL
 rows:292004
 Extra:Using where

Then i have executed the query below.

 EXPLAIN SELECT * FROM table1 WHERE col1='test' AND col2='test'

Result is:

 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

I am using FORCE INDEX statement in SP. But it insists on not using index. Any idea? I think i am close to end :)

Answer 1

Just a guess:

When you run the query by hand, the expression WHERE ('test' IS NULL or COL1 = 'test') can be optimized when the query is being parsed. The parser can see that the string 'test' is not null, so it converts the test to WHERE COL1 = 'test'. And if there's an index on COL1 this will be used.

However, when you create a stored procedure, parsing occurs when the procedure is created. At that time, it doesn't know what @param will be, and has to implement the query as a sequential scan of the table.

Try changing your procedure to:

IF @param IS NULL
THEN BEGIN
  SELECT * FROM table1
  UNION ALL
  SELECT * FROM table2
  ...
END;
ELSE BEGIN
  SELECT * FROM table1 WHERE col1 = @param
  UNION ALL
  SELECT * FROM table2 WHERE col1 = @param
  ...
END;
END IF;

I don't have much experience with MySQL stored procedures, so I'm not sure that's all the right syntax.