Bug #67634 Poor execution order of "create temporary table if not exists ... select ..."

• Submitted: 19 Nov 2012 14:22
• Modified: 27 Nov 2012 20:48
• Reporter: f l
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.1.54
• OS: Any
• CPU Architecture: Any
• Tags: create table execution order

[19 Nov 2012 14:22] f l

Description:
Assume the following query with a complex and long-running select statement:

"CREATE TEMPORARY TABLE IF NOT EXISTS foo SELECT ..."

When this query is called multiple times (using the same connection) the select statements gets reexecuted. Afterwards it is checked for the existence of the temporary table. 

How to repeat:
CREATE TEMPORARY TABLE IF NOT EXISTS foo SELECT sleep(10);
CREATE TEMPORARY TABLE IF NOT EXISTS foo SELECT sleep(10);

=> mysql is executing both sleeps :(

Suggested fix:
Wouldn't it be faster to first check for the existence of the table and if so do not execute the select statement?

[27 Nov 2012 20:48] Sveta Smirnova

Thank you for the report.

This is fixed in version 5.5. Please upgrade.