17

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 SP Profile Information

Query Profile Informations enter image description here

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 :)

BlueTrin
  • 9,610
  • 12
  • 49
  • 78
bselvan
  • 384
  • 1
  • 4
  • 14
  • 1
    It could be that after executing the SP, MySQL has cached the result, and then when you execute it outside the SP, it's just hitting the cache rather than executing it again. – Steven Moseley May 25 '13 at 19:06
  • By the way, why 10 tables with the same structure? Why not combine them into 1 table? – Steven Moseley May 25 '13 at 19:07
  • database design is out of my hand i would never do such design :) first i execute the query with different parameter then immediately i call the sp with same parameter. result same. it seems sp didnt even take the result from cache. – bselvan May 25 '13 at 19:15
  • When you execute it as a normal query do you replace BOTH occurances of @param1 on each sub query with 'test', or just use the (col1 = 'test'). If so suspect mysql might be failing to use an index in the stored proc due to the OR, but using one in the query – Kickstart May 25 '13 at 19:28
  • Does SP with `FORCE INDEX` work faster? The query optimiser may be not so good as in other databases and creates plan _before_ substituting parameters with their values – Stoleg May 29 '13 at 20:53
  • No. With or without force index ,execution time and plan is same. It seems sp dont use index at all. – bselvan May 29 '13 at 21:02
  • Just realised there is no table or index definition. Can you please post index definition, and check that every table actually has _exactly_ same index. – Stoleg Jun 04 '13 at 05:38

4 Answers4

9

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks for the answer. I will give a try. Actually my where clause is not so short. (@param1 is null or col1=@param1) and (@param2 is null or col2=@param2) and .... to param6. For being clear, i didnt write so detailed.sorry. i think in this sitution your solution does not exactly cover my question. – bselvan May 25 '13 at 20:37
  • The difference comes from using variables as `@Var is null or col1=@var`. Run EXPLAIN on both: `EXPLAIN SELECT * FROM MY_VIEW` and `SELECT * FROM SP_MY_PROC`. It should give the reasons why. In SQL Server there is and option RECOMPILE to use in such cases to recompile execution plan. I'm looking but not finding for something like that in MySQL. – Stoleg May 28 '13 at 21:06
  • @Stoleg That was my original explanation, but he's gotten rid of the `OR` in his latest version. So I don't know why the SP would still be slow. – Barmar May 28 '13 at 21:10
  • @Barmar, Sorry for that. EXPLAIN should give some ideas about the reason. Actually, from timings provided it can be that creation of query plan in case of parameters takes so long. [Read about Controlling Plan Evaluation](http://dev.mysql.com/doc/refman/5.5/en/controlling-query-plan-evaluation.html). It can take longer to create execution plan then to execute the query itself. – Stoleg May 28 '13 at 21:31
  • @Barmar, You cannot run Explain on SP. However you can create a view or run explain on the query with multiple unions. From the timings it looks to me that it is pre-execute stage that's taking too long. – Stoleg May 28 '13 at 21:41
  • @Stoleg The problem he's reporting is that the exact same query takes 20x longer when it's in a SP. There's no unions, it's just `SELECT * FROM table WHERE col1=param1 AND col2=param2`. – Barmar May 28 '13 at 21:47
  • @Barmar, probably I should rephrase. Is it possible to run `set @param1='test'; set @param2='test'; EXPLAIN SELECT * FROM table WHERE col1=@param1 AND col2=@param2`? – Stoleg May 28 '13 at 21:57
  • Your edited question is very different from the one I originally answered. I am very inexperienced with stored procedures, so I am not even attempting to answer it now. – Barmar May 29 '13 at 14:35
8

Possible character set issue? If your table character set is different from your database character set, this may be causing a problem.

See this bug report: http://bugs.mysql.com/bug.php?id=26224

[12 Nov 2007 21:32] Mark Kubacki Still no luck with 5.1.22_rc - keys are ingored, query takes within a procedure 36 seconds and outside 0.12s.

[12 Nov 2007 22:30] Mark Kubacki After having changed charsets to UTF-8 (especially for the two used), which is used for the connection anyways, keys are taken into account within the stored procedure!

The question I cannot answer is: Why does the optimizer treat charset conversions an other way within and outside stored procedures? (Indeed, I might be wrong asking this.)

Community
  • 1
  • 1
Bumptious Q Bangwhistle
  • 4,689
  • 2
  • 34
  • 43
  • thank you so much. The reason is exactly same. My table and database character set was different. I have changed both of them same, then the index has been used in sp. – bselvan Jun 18 '13 at 10:25
0

Interesting question, because I am fond of using stored procedures. Reason is maintenance and the encapsulation principle.

This is information I found: http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html

It states that the query cache is not used for queries that 1. are a subquery that belong to an outer query, and 2. are executed within the body of a stored procedure, trigger or event.

This implies that it works as designed.

Loek Bergman
  • 2,192
  • 20
  • 18
0

I had seen this behavior, but it wasn't related to the character set.

I had a table that held self-referencing hierarchical data (a parent with children, and some children had children of their own, etc.). Since the parent_id had to reference the primary id's (and the column specified a constraint to that effect), I couldn't set the parent id to NULL or 0 (zero) to disassociate a child from a parent, so I simply referenced it to itself.

When I went to run a stored procedure to perform the recursive query to find all children (at all levels) of a particular parent, the query took between 30 & 40 times as long to run. I found that altering the query used by the stored procedure to make sure it excluded the top-level parent record (by specifying WHERE parent_id != id) restored the performance of the query.

The stored procedure I'm using is based on the one shown in: https://stackoverflow.com/questions/27013093/recursive-query-emulation-in-mysql.

Community
  • 1
  • 1
Big_Al_Tx
  • 954
  • 9
  • 14