Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to upgrade our database servers to MySQL 5.5. In doing so, I'd like to convert all of our tables to InnoDB to take advantage of several performance benefits.

However, we use VARCHAR(50) fields for all of our primary keys. From what I've read about InnoDB indexes, the shorter the primary key the better, since all secondary keys reference the primary key. So, instead of converting the tables by issuing an ALTER TABLE tablename ENGINE = InnoDB;, I'd like to create a new temporary table with all of the fields from the old table plus a new autoincrement primary key. The old primary keys will become unique keys. Then, I'll insert all rows into the new table, drop the old table, and rename the new table to the old.

However, I need a clever query to do so, because insert into tablename_temp '',* from tablename; generates a syntax error (I assume because I can't select anything with *). I'm trying to come up with an alternative, but I can't figure it out.

Short question: How can I insert into tablename_temp '',* from tablename;

Any input is much appreciated!

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

You can do the following:

CREATE TABLE tablename_temp (id int AUTO_INCREMENT PRIMARY KEY)
ENGINE=InnoDB 
SELECT * FROM tablename;

ALTER TABLE b
ADD UNIQUE INDEX some_index_name (your_original_key);

You can find the documentation here: http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html And a small example on SQLFiddle.

share|improve this answer
    
Thanks for the comment. I'm worried that the only way to best handle the conversion of the old PKs to unique keys is to do that manually? Or will it automatically be handled here? –  Christopher Armstrong Sep 4 '12 at 19:11
    
New SQLFiddle: sqlfiddle.com/#!2/a24bf/1 –  Christopher Armstrong Sep 4 '12 at 19:13
    
I updated my answer (simply forgot to include the original key). Updated the link to my fiddle as well. –  dezso Sep 4 '12 at 19:17
    
Thanks. I'm going through and manually adding the indexes after each insert. –  Christopher Armstrong Sep 4 '12 at 20:13
1  
Check the answers: stackoverflow.com/questions/893874/… You can generate those ALTER TABLE pieces, no need to copypaste a million times :) –  dezso Sep 4 '12 at 20:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.