Why does MySQL create a temp table when adding an index?

[mos]

I don't understand why MySQL will create a copy of the table when I'm 
adding an index to it.

Example:

Alter Table Tablex Add Index NewIndex (col1,col2);

ProcessList will show "copy to tmp table" for 99% of the process.

This will physically copy the data to a temporary table. I have a 7gb table 
and adding an index takes a long long time. I don't see why the index can't 
be created without physically copying the data. If the index operation 
fails, then just delete the index. I don't see why creating an index needs 
to alter the data file. If it doesn't alter the data file, why copy it? I 
understand the need to copy the data when the Alter Table is changing the 
field definitions, but why do it when creating an index?

Can someone shed some light on this? TIA

Mike



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]