MySQL Bugs: #39833: CREATE INDEX does full table copy on TEMPORARY table

Bug #39833	CREATE INDEX does full table copy on TEMPORARY table
Submitted:	3 Oct 2008 9:23	Modified:	27 Oct 2008 15:34
Reporter:	Marko Mäkelä	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S5 (Performance)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When creating an index on a temporary table, MySQL 5.1 will not call handler::add_index(), but it will copy the table instead. This is somewhat related to Bug #33650.

How to repeat:
Use a storage engine that supports fast index creation. For example, with the InnoDB plugin, set breakpoints on ha_innodb::add_index and ha_innodb::delete_table and run the following:

create temporary table a(a int primary key, b char(1)) engine=innodb;
create index b on a (b);
drop table a;

create table a(a int primary key, b char(1)) engine=innodb;
create index b on a (b);
drop table a;

The first "create index" will not invoke handler::add_index(), but the second one will. The first "create index" is dropping a #sql... temporary table but the second one is not.

Suggested fix:
Allow fast index creation on temporary tables too.

Thank you for the bug report. Verified as described:

mysqld.exe!ha_innobase::delete_table(const char * name=0x01f56ea8)  Line 6042	C++
mysqld.exe!handler::ha_delete_table(const char * name=0x01f56ea8)  Line 3277 + 0x14	C++
mysqld.exe!rm_temporary_table(handlerton * base=0x0124ef60, char * path=0x01f56ea8)  Line 5488 + 0x12	C++
mysqld.exe!close_temporary(st_table * table=0x01f565b0, bool free_share=true, bool delete_table=true)  Line 1889 + 0x15	C++
mysqld.exe!close_temporary_table(THD * thd=0x01ef1928, st_table * table=0x01f565b0, bool free_share=true, bool delete_table=true)  Line 1866 + 0x11	C++
mysqld.exe!mysql_alter_table(THD * thd=0x01ef1928, char * new_db=0x01f4f148, char * new_name=0x01f4ef40, st_ha_create_information * create_info=0x0392f084, TABLE_LIST * table_list=0x01f4ef68, Alter_info * alter_info=0x0392f024, unsigned int order_num=0, st_order * order=0x00000000, bool ignore=false)  Line 6855 + 0x11	C++
mysqld.exe!mysql_execute_command(THD * thd=0x01ef1928)  Line 2636 + 0x2f	C++
mysqld.exe!mysql_parse(THD * thd=0x01ef1928, const char * inBuf=0x01f4eeb8, unsigned int length=23, const char * * found_semicolon=0x0392fd00)  Line 5727 + 0x9	C++
mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x01ef1928, char * packet=0x01f46de9, unsigned int packet_length=23)  Line 1152 + 0x1e	C++
mysqld.exe!do_command(THD * thd=0x01ef1928)  Line 809 + 0x1b	C++
mysqld.exe!handle_one_connection(void * arg=0x01ef1928)  Line 1115 + 0x9	C++
mysqld.exe!pthread_start(void * param=0x01247850)  Line 85 + 0x9	C
mysqld.exe!_threadstart(void * ptd=0x01f16750)  Line 196 + 0xd	C
kernel32.dll!7c80b713() 	

mysqld.exe!ha_innobase::add_index(st_table * table=0x01f2d2f0, st_key * key_info=0x01f4f390, unsigned int num_of_keys=1)  Line 616	C++
mysqld.exe!mysql_alter_table(THD * thd=0x01f26628, char * new_db=0x01f4ebe0, char * new_name=0x01f4e9d8, st_ha_create_information * create_info=0x0392f084, TABLE_LIST * table_list=0x01f4ea00, Alter_info * alter_info=0x0392f024, unsigned int order_num=0, st_order * order=0x00000000, bool ignore=false)  Line 6785 + 0x28	C++
mysqld.exe!mysql_execute_command(THD * thd=0x01f26628)  Line 2636 + 0x2f	C++
mysqld.exe!mysql_parse(THD * thd=0x01f26628, const char * inBuf=0x01f4e950, unsigned int length=23, const char * * found_semicolon=0x0392fd00)  Line 5727 + 0x9	C++
mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x01f26628, char * packet=0x01f467a9, unsigned int packet_length=23)  Line 1152 + 0x1e	C++
mysqld.exe!do_command(THD * thd=0x01f26628)  Line 809 + 0x1b	C++
mysqld.exe!handle_one_connection(void * arg=0x01f26628)  Line 1115 + 0x9	C++
mysqld.exe!pthread_start(void * param=0x01f09080)  Line 85 + 0x9	C
mysqld.exe!_threadstart(void * ptd=0x01f41ef0)  Line 196 + 0xd	C
kernel32.dll!7c80b713()