[SOLVED] index time

[jaymc]

I have a table with 7.5 Million rows

 

only 4 fields, 2 varchar(25) and the others int(12)

 

It was originally myisam, but I wanted to convert it to INNODB

 

I tried using the ALTER TABLE to do a straight convert but after 4 hours of it running and bringing the server to a halt I give up

 

I realised it was probably due to it having to reindex after every insert, so I created another table with innodb engine, inserted all the rows accross with no index and that worked fine, took about 3 minutes

 

Now, I need to add my index back, its been running for 2 hours now on the varchar(25)

 

Is this just how it is since the table is 7.5million rows?

[fenway]

I don't think I've ever had that many rows in a table... I'd be curious if it would take the same time to re-build an myisam index.

[jaymc]

I think it took me 5-10 minutes last time, assuming innodb not too good with altering or adding indexes etc

[fenway]

I think it took me 5-10 minutes last time, assuming innodb not too good with altering or adding indexes etc

Well, the index structure is fundamentally different.

 

Wait a minute, is binary logging enabled?

[jaymc]

No its not

[jaymc]

Im sitting on 2x 1.4Ghz cpu with 3GB ram

 

Here is everything from my.cnf related to innod

 

innodb_data_file_path = ibdata1:500M:autoextend
innodb_buffer_pool_size = 2500M
innodb_additional_mem_pool_size = 2M
innodb_flush_method = O_DIRECT

 

Perhaps Im hitting some kind of configuration bottle knecks?

 

 

[fenway]

You may want to read this.