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!