MySQL Bugs: #21013: Performance Degrades when importing data that uses Trigger and Stored Procedure

Bug #21013	Performance Degrades when importing data that uses Trigger and Stored Procedure
Submitted:	12 Jul 2006 19:08	Modified:	15 Aug 2006 11:37
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	>= 5.0.18	OS:	All OS
Assigned to:	Tomash Brechko	CPU Architecture:	Any
Tags:	degradation, degrades, import, importing, performance, stored procedure, trigger

Description:
When performing an import on a table with a trigger that calls a stored procedure, the performance time of this increases dramatically from version 5.0.17 to 5.0.18.

Please see the details/files I have marked as private.

In the commands below, the first step just creates the database, trigger, and stored procedure.  We are not concerned with this step.

The second step is the problem.

I have tested this on several versions of 5.0.x.

5.0.16	< 1 second
5.0.17	< 1 second
5.0.18	~ 47 seconds
5.0.22	~ 47 seconds
5.0.23	~ 47 seconds

I have verified this on Windows, and the customer stated they experienced the same problem on FreeBSD, Linux, and Windows.

How to repeat:
shell> mysql < test_slow_trigger.sql
shell> mysql slowtest < slowtest.sql

Suggested fix:
Not sure.  Although I first thought it was related to bug #17260, but that bug fix did not help with this particular problem.

However, from looking at the 5.0.18 changelogs, it appears to have something to do with the changes to how the server treats stored routine parameters and local variables (and stored function return values) according to standard SQL.

http://dev.mysql.com/doc/refman/5.0/en/news-5-0-18.html

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9179

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9219

Pushed into 5.0.25

Merged into 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25/5.1.12 changelogs.