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 Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.54 OS:Any
Assigned to: 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.