At 04:29 PM 6/10/2006, you wrote:
Hi, Dan,RE: > Currently mysql handles all "ALTER TABLE" commands by rebuilding the > entire table and all indexes. OK, so an "add index" is mapped to "alter table", and the "alter table" rebuilds everything. This means if I have a table with multiple indexes, it does not make too much sense to create the indexes separately? For example: alter table add index name1 (name1); alter table add index name2 (name2); alter table add index name3 (name3); Instead, something like: alter table add index name1 (name1), add index name2 (name2), \ add index name3 (name3); should work better.
Correct. When using Alter on a table, make sure all table changes are done through 1 Alter command otherwise the data gets reloaded each time the Alter is run.
I don't know why adding an index or dropping an index MySQL has to copy the table and reload all of the data back into the table. It just doesn't make any sense. It can lock the table sure, but why reload the data? All it has to do is copy the index that is being modified so it can roll it back, but don't copy all the data and rebuild all the indexes. It's grossly inefficient if you ask me. :(
Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
