Duplicating entire rows in MySQL table to the same table

On a recent large scale web application we were developing at WebCastle, I came across a need to clone complete rows from a MySQL table to same table. Initially we were having 2.5 million product rows in the table. Our requirement was to test our application performance with Sphinx Search engine to load it with 1 million products. Client didn’t had that much products ready yet and we wanted to make it rocket fast and bulletproof.

There were suggestions around the web to copy the rows in to new table and then copy back to original table to avoid duplicate key issue for indexed ID ( primary key ). But I just have a simple idea.  Just below query will do the job of cloning entire rows in to the same table without conflicting primary key

INSERT INTO `TABLENAME` (`field2`, `field3`,… ) SELECT `field2`, `field3`,… FROM TABLENAME

Replace TABLENAME with your table’s name. Care that I kept field2 as the first column name which means, you should not insert in to ID column or select data from it. Just copy the rest and ID will auto increment.

There is one more thing

To get the complete field names within a table, you can just export the table with only one row in it. Again remember to delete primary key field name.