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! Movies - coverage of the 74th Academy Awards® http://movies.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 --------------------------------------------------------------------- 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
