Creating Keys on Table has been running five days.

[Bitswap]

I hope someone can help me with this.

I have a table that consists of two int types. I've imported 136 million records into the table which took 2.5 days. Now I'm altering the table by adding a primary key. The primary key are both fields. This has been running for five days. The processor is 2 gig mhz with 512 meg of ram, W2k os.

I tried to import the records with the primary key defined, but after two days it only processed 11 million records and was getting progressively slower.

The AS400 guys say this process takes them about an hour to complete. But they're not using mysql.

Is there a quicker way to do this or am I making doing something wrong?

Thanks

[raf]

Welcome here.

I think that you should, in any case, first create the table with the primary key on an autonum column, and then import the records. I don't see why you should create a primary key on the 2 columns ?? Since there are only two, just ad a third column that holds the primary key value. I don't think you can create a primary key on other columns, like you create a foreign key or so. I don't even think MySQL allows composit keys.

136 million of records ... What are you gonna do with that ? I can't image any perfomant searches on that. Can't you split it up over several tables with some sort of little queryanalysing module in your application.

And maye plug in some extra RAM ...

[angrytuna]

Hate to be disagreeable, but I have to contradict the above post. Primary keys can indeed be created in MySQL consisting of more than one attribute: see here[mysql.org] (http://www.mysql.com/doc/en/CREATE_TABLE.html)
for usage and syntax.

If you can define a key that is unique with two coupled attributes, it would be preferred over adding extraneous information.

Other than that, I haven't worked with imports of that size before, so I'll pass the original question on to someone else.

AT

[Bitswap]

The problem is solved.

Odd, I saw this before but the import file was fixed length. After obtaining a delimineted file I was able to use it.

The command was LOAD DATA INFILE. It brought in the data with the primary key in just under two hours.

Thanks for the replys, appreciate the help.