20

Is there a more-efficent, less laborious way of copying all records from one table to another that doing this:

INSERT INTO product_backup SELECT * FROM product

Typically, the product table will hold around 50,000 records. Both tables are identical in structure and have 31 columns in them. I'd like to point out this is not my database design, I have inherited a legacy system.

Romil Kumar Jain
  • 20,239
  • 9
  • 63
  • 92
crmpicco
  • 16,605
  • 26
  • 134
  • 210
  • 5
    I thinks this is the best way. In this way you are preserving indexes also. – Romil Kumar Jain Jun 25 '12 at 11:28
  • 1
    Speedwise that's about as good as it gets. You can of course defer index creation on the backup table until after all data have been copied, that will significantly increase insertion speed. – fvu Jun 25 '12 at 11:30
  • That's interesting, thanks. I was inquisitive around the copying of data and wondered if this was either a drain on the database (the `SELECT *` threw me off, or if it would take a long time to process because of the way the query is constructed). If this is an acceptable way of copying the data across then that's fine. – crmpicco Jun 25 '12 at 12:33
  • I think you might run into trouble if you have millions of rows. In such a case you can use the dump/load solution. – mihaisimi Jun 25 '12 at 12:46
  • I won't ever have millions of rows in this table. I can't see there ever being > 100,000 – crmpicco Jun 25 '12 at 12:54
  • @Romil Happy to accept yours as the answer if you want to submit one. – crmpicco Jun 29 '12 at 11:31
  • I just ran the same command for 200 million rows and it worked alright. The only thing you'd also need to take care is - mysql execution timeout which can be changed my workbench.I've set it to 24 hours. This is an obsolete database and has no users at the moment nor is the system above it active. – BRBdot Oct 09 '18 at 22:39

4 Answers4

19

There's just one thing you're missing. Especially, if you're using InnoDB, is you want to explicitly add an ORDER BY clause in your SELECT statement to ensure you're inserting rows in primary key (clustered index) order:

INSERT INTO product_backup SELECT * FROM product ORDER BY product_id

Consider removing secondary indexes on the backup table if they're not needed. This will also save some load on the server.

Finally, if you are using InnoDB, reduce the number of row locks that are required and just explicitly lock both tables:

LOCK TABLES product_backup WRITE;
LOCK TABLES product READ;
INSERT INTO product_backup SELECT * FROM product ORDER BY product_id;
UNLOCK TABLES;

The locking stuff probably won't make a huge difference, as row locking is very fast (though not as fast as table locks), but since you asked.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • I am using the MyISAM engine. I mentioned in an earlier post that I have inherited a legacy system, so it's MyISAM for the moment. – crmpicco Jun 25 '12 at 14:33
  • couldn't ordering that create an index tree that is unbalanced? In which case random order may be better for the primary key? – Danny Staple Aug 20 '13 at 10:29
  • 1
    @DannyStaple, the tree is flat when inserting into an index in sorted order with MyISAM. This improves performance (not having to rebuild index), as well as saves space. From [MySQL documentation](http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html): "When rows are inserted in sorted order (as when you are using an AUTO_INCREMENT column), the index tree is split so that the high node only contains one key. This improves space utilization in the index tree." – Marcus Adams Aug 20 '13 at 12:13
  • The second row in the second code block should read `LOCK TABLES product;`, `product_id` is the column. – Torque May 31 '19 at 09:35
5
mysqldump -R --add-drop-table db_name table_name > filepath/file_name.sql

This will take a dump of specified tables with a drop option to delete the exisiting table when you import it. then do,

mysql db_name < filepath/file_name.sql
A.L
  • 10,259
  • 10
  • 67
  • 98
satdev86
  • 800
  • 7
  • 14
  • I should have added that I am doing this in PHP code. It will be done before a series of INSERTs and UPDATEs are carried out on the `product` table, so I would be looking to do it in PHP code rather than MySQL administration functions. – crmpicco Jun 25 '12 at 12:30
2

DROP the destination table: 

DROP TABLE DESTINATION_TABLE;
CREATE TABLE DESTINATION_TABLE AS (SELECT * FROM SOURCE_TABLE);
elMarquis
  • 7,450
  • 4
  • 38
  • 42
Ice Cream
  • 31
  • 3
  • 1
    what if there is data already in the destination table that OP wants to preserve and add to? – Martin Nov 28 '16 at 10:30
  • why drop and create table instead of DELETE FROM destination_table; INSERT INTO destination_table SELECT * FROM product? Which one is more efficient? – Roberto Sepúlveda Bravo Jul 14 '17 at 16:02
  • 1
    Drop will reset primary auto increment ids – Epirocks Nov 06 '18 at 10:52
  • Using this for Django managed DB broke the table. Even though the primary key and auto increment directives were correctly setup, new rows were being added with same primary key! (without incrementing). But weirdly I could not delete the rows once they were added as it would raise 'Unexpected update count received' error. I had to delete and restore my schema to fix this issue. – gowthz Nov 23 '21 at 06:06
1

I don't think this will be worthy for a 50k table but: If you have the database dump you can reload a table from it. As you want to load a table in another one you could change the table name in the dump with a sed command: Here you have some hints: http://blog.tsheets.com/2008/tips-tricks/mysql-restoring-a-single-table-from-a-huge-mysqldump-file.html

An alternative (depending on your design) would be to use triggers on the original table inserts so that the duplicated table gets the data as well.

And a better alternative would be to create another MySQL instance and either run it in a master-slave configuration or in a daily dump master/load slave fashion.

mihaisimi
  • 1,911
  • 13
  • 15