Use another disk for "optimize table" temporary file I have a large table (70 Go) on a disk with only 9 Go free space, which I need to optimize ("optimize table ..").

I have changed the "tmpdir" parameter so that the temporary file is created on another disk with enough space.

However the temporary file "#sql..." is still created in the mysql data folder, where tables are located.

Is there a way to tell mysql to create the temporary file on another disk for "optimize table"?

If not, is the only solution to transfer the table onto another disk, run "optimize table" there, then replace the old table file?

Here is the row returned by "SHOW TABLE STATUS" for the table:

Engine  Version Row_format  Rows    Avg_row_length  Data_length Max_data_length Index_length    Data_free   Auto_increment  Create_time Update_time Check_time  Collation   Checksum    Create_options  Comment
InnoDB  10  Compact 218780456   176 38628507648 0   18179555328 17278435328 290029517   2016-02-25 16:33:50 NULL    NULL    utf8_general_ci NULL
If you have deleted most of the rows from a table, OPTIMIZE table needs only enough room to build a new, smaller, copy.  After the copy is finished, it will rename, then drop the old table.

If the table had its own tablespace (built with innodb_file_per_table=ON), then the freed space will be returned to the OS.  But...  The 17GB free in the .ibd cannot be used for the table copy.  The copy will be in a separate file.  So it could fail if the resulting table is more than 8GB (data+index).

If =OFF, the OPTIMIZE will try to use free space in ibdata1 before increasing the size of that file.

There is 17GB free somewhere; I can't tell where.

Also, check TABLE STATUS for other tables.  The Data_free for all tables in ibdata1 will be the same value.  What is that value?

There are tricks to work around the cramped space, but I need answers to the questions posed.