Hi,
If I have a large table (> 70million rows, nearly 4 GB Index, 2.5G Data),
and I want to
ALTER TABLE mytable TYPE=MYISAM;
ALTER TABLE mytable change column col1 col1 tinyint unsigned not null;
ALTER TABLE mytable DROP INDEX index_1;
ALTER TABLE mytable ADD INDEX index_1(col2, col1);
ALTER TABLE mytable ADD INDEX index_2(other_cols......)
ALTER TABLE mytable ADD INDEX index_3(more_cols......)
am I going to be any better off doing it by:
1. Drop all indexes, change table type, recreate indexes, and run other
alters
2. Create a new table with required structures, and insert into .... select
...
3 Create a new table, and mysqldump db mytable | mysql db newtable
4 2 or 3, but create the indexes (5 indexes) after doing the import
5. Run all the alter tables at once
6 Run the alter tables one after the other.
In this respect, and as a matter of general interest, if I issue the command
ALTER TABLE mytable add index i1(c1, c2), add index i2(c3, c4), change
column c1 c1 column_definition
what does the server do - all in one hit, sequentially, with/without indexes
for the 'temporary table'?
Thanks
P.S. I am subscribed to the digest, but not the list, so copy replies to me
please. Thanks again
Quentin Bennett
Transport Systems Division
Infinity Solutions
web http:\\www.infinity.co.nz
mailto:[EMAIL PROTECTED]
Phone : +64 9 358 9720
Fax : +64 9 309 4142
The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended
recipient, you are asked to respect that confidentiality and not
disclose, copy or make use of its contents. If received in error
you are asked to destroy this email and contact the sender immediately.
Your assistance is appreciated.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
