Hi Rolando, I am going to give it a try, but the thing is that the creation of index with MyISAM table causes a re-copy of the table (using temporary table) and so it is the same thing, and seens to take a lot of time. I stopped it after 10 hours or so. I think is the way mysql manages the creation of indexes.
Anyway thank you and I will update you! Claudio 2009/2/27 Rolando Edwards <redwa...@logicworks.net> > 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 >
