On Wed, Sep 19, 2001 at 03:14:17PM +1200, Quentin Bennett wrote:
> 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.
What a great test to run someday. :-)
If I had to do it, I'd go with option #1 or option #3. But that's a
gut feel that I can back up with much in the way of experience.
> 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'?
No idea... Someone else will tell us, hopefully.
> web http:\\www.infinity.co.nz
The slashes got he other way.
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 13 days, processed 238,884,989 queries (212/sec. avg)
---------------------------------------------------------------------
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
