Description:
allow user to specify alternate location for temp file during 'copy to tmp table' activity, caused by 'ALTER TABLE' statement...
How to repeat:
initiate a table/index-altering (non-renaming/non-relocating) action on a db table using 'ALTER TABLE'command, such as enabling/adding indexes...
- a tmp table file copy will be created in same directory where the original table's file is stored.
Suggested fix:
allow user to specify an alternate location (i.e. drive) where temp files should be created when a tmp table copy is instigated by the 'ALTER TABLE' command. location could be specified either globally (my.cnf?), session variable, or possibly as extra parameter to ALTER TABLE...
justification:
for large database tables, creating a complete table copy within the same directory/location as the original table causes a large amount of IO on the same physical disk/volume for both the 'read from original' & 'write to temp copy' operations.
it would be much more efficient to be able to specify that the tmp table copy be created elsewhere (on separate, faster storage), thus avoiding the unecessary IO bottleneck on the original db file's physical drive/volume.
