Have you tried disabling indexes while loading? Here is what I mean... CREATE TABLE tb1 (A INT NOT NULL AUTO INCREMENT PRIMARY KEY,B VARCHAR(20),C VARCHAR(10));
Load tb1 with data Create a new table, tb2, with new structure (indexing B and C columns) CREATE TABLE tb2 LIKE tb1; ALTER TABLE tb2 ADD INDEX NDX1 (B); ALTER TABLE tb2 ADD INDEX NDX2 (C); Load tb2 with non-unique indexes turned off ALTER TABLE tb2 DISABLE KEYS; INSERT INTO tb2 SELECT * FROM tb1; Only the Primary Key got loaded in tb2 Now build the other two indexes ALTER TABLE tb2 ENABLE KEYS; This should build the indexes linearly, loading key entries into the .MYI file of the MyISAM table. Give it a try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -----Original Message----- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Friday, February 27, 2009 4:43 PM To: mysql@lists.mysql.com Subject: MyISAM large tables and indexes managing problems Hi, I have one 15GB table with 250 million records and just the primary key, it is a very simple table but when a report is run (query) it just takes hours, and sometimes the application hangs. I was trying to play a little with indexes and tuning (there is not great indexes to be done though) but eveytime I try to alter table for indexes it just hogs the disk space and takes hours to try to build indexes in various passages(.TMD) but it is a real pain since I cannot even kill the mysql process, and I had to kill the server with table corruption and had to stop/start and repair table. Does anybody experience problems in managing a simple MyISAM table with 250 million records and a primary key? I tried also to duplicate the table, add indexes and insert into it (also using INNODB for the new table) but it is really taking ages everytime. And I had to move the 'tmpdir' to the data partition because it was filling the / 100%. MySQL is 5.0.x on 64bit RHEL 5 with 16GB RAM and NAS storage. Any hint on how to manage big tables? Thanks Claudio Nanni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
