MySQL Create Table Like with Engine override

5 Views

I am in the process of breaking up a wordpress site with one database and 81,000+ tables into a multi-DB using a plugin by wpmudev.org. The main motivator is performance and I checked the tables itself and they are of type MyISAM.

I was thinking that while moving the tables into new databases I could also change the table type to InnoDB which should see a site performance improvement.

Read More

The script to migrate tables from the existing db to the new db uses the following syntax:

CREATE TABLE …. LIKE ….
INSERT INTO …. SELECT * FROM ….

I could not locate information if I could oveerride the engine in the CREATE TABLE command such as:

CREATE TABLE …. LIKE …. ENGINE=InnoDB

Also I am wondering if “INSERT INTO …. SELECT * FROM ….” is the most efficient way to insert the data… This is a php script so I don’t mind adding a little code to improve the performance which – at an earlier trial took 3 days to run on a 4-core 4GB RAM!

Post Views: 5

Related posts

Leave a Reply

4 comments

  1. You can’t specify ENGINE parametr with create table like... syntax, but here are some alternatives:

    CREATE TABLE `another_table` ENGINE=MEMORY SELECT * FROM `original`

    … will copy table structure and data, without keys and trigers

    CREATE TABLE `another_table` ENGINE=MEMORY SELECT * FROM `original` WHERE 0

    … will copy just structure, but no data (because WHERE 0 will filter all out)

    CREATE TEMPORARY TABLE `another_table` ) ENGINE=MEMORY SELECT * FROM `original`

    … maybe, you would like to add TEMPORARY word, then DB would automatically remove the table when you close the connection

    CREATE TABLE `another_table` ( INDEX(`id_column`) ) ENGINE=MEMORY SELECT * FROM `original`

    … and this is an example on how to recreate the keys in new table (syntax of defining keys as the same as in simple CREATE TABLE syntax)

  2. Yes, I’ve done just this (override the table type). Works fine.

    For big transfers like this I’ve generally done some form of database dump,
    a script to massage the data (e.g. change engine types), then a restore to the new database. The text based database dumps mostly use COPY which is faster than INSERT INTO.

    You can also issue ALTER TABLE {} ENGINE=INNODB;. Though that said a fresh start has a lot going for it also.

    81,000 tables. Wow.