Dave,
Thanks for your response. The problem with that, while it is technically one insert statement, mySQL still builds the primary key record-by-record (keycache), rather than sorting the keys and creating the index in one shot, like myisamshk can do. "INSERT..." would take upwards of 24 hours to complete on my table of 18 million records, while myisamchk can do it in about 10 minutes. I know my method works, but I wanted to confirm that it was acceptable, and I wasn't missing anything serious. -Hank > Instead of dropping down to OS cp commands, insert the data from > table TBL_FLAT to TBL_INDX. > > INSERT INTO TBL_INDX SELECT * FROM TBL_FLAT; > > This will be valid in mysql. The insert will be fast since > only one insert statement. Let the list know if this works > for you. Or if your method is valid as well. > > David > > -----Original Message----- > From: Henry Hank [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, March 26, 2002 3:37 PM > To: [EMAIL PROTECTED] > Subject: question about converting isam to myisam tables (shotcut!) > > Hello all, > > I'm in the process of building a new mysql db server box to migrate a > database off an existing web server. The webserver is running mysql 3.22.29, > and the new box is running 3.23. The two biggest tables are about 20 million > records and the data and index files are each just over 1GB, and each have a > primary key and three or four non-unique indexes. > > I've copied the data files from the old box to the new, but when I use the > ALTER TABLE...TYPE=MYISAM, it takes forever since it is rebuilding the > indexes by keycache. So this is what I'm doing instead, and I'm asking if > there are any problems associated with this approach: > > - the existing ISAM Table is called TBL_ISAM > - Create a new/empty myisam table with no indexes named TBL_FLAT > - Create a new/empty myisam table with all indexes named TBL_INDX > > mysql> insert into TBL_FLAT select * from TBL_ISAM; (runs very quickly - not updating any indexes) > mysql> flush tables; > > the in the OS shell (linux) do the following: > > $ cp TBL_INDX.frm TBL_FLAT.frm > $ cp TNL_INDX.MYI TBL_FLAT.MYI > > then to rebuild all the indexes via sorting, use myisamchk: > > $ myisamchk -r -q TBL_FLAT > > I'm left with a fully populated and indexed TBL_FLAT table, which seems to > work perfectly as the original converted ISAM table. > > An in addition, I did first try "myisamchk -r -k0" to disable the indexes and > do the insert into the TBL_INDX table, but mysql still insists on building > the primary key via the keycache - and it takes way too long. My method allows > me to populate the data file with no indexes, and then fool mysql into > rebuilding all the indexes at once via sorting. > > Does anyone think there are any risks/problems with this approach? > > Thanks in advance! > -Hank > __________________________________________________ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.yahoo.com/ --------------------------------------------------------------------- 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
