A Few MySQL Tricks

Here are a few tricks I use quite a lot with MySQL.

1) copy an existing table's schema to a new table

This is handy for making a quick copy of an existing table to test indexes, populate data, etc...

CREATE TABLE NewTableName LIKE OldTableName;

2) copy an existing table to a new table

If you want more than just the structure, you can copy a table's raw data to a new table really easily. The downside is that keys and other constraints aren't preserved.

CREATE TABLE NewTableName SELECT * FROM OldTableName;

3) copy a table's exact structure and data

If you want to preserve everything (constraints, keys, etc...) and copy the data, it requires two steps.

CREATE TABLE NewTableName LIKE OldTableName;
INSERT INTO NewTableName SELECT * FROM OldTableName;

4) quickly load data while specifying columns

After reading the MySQL forums, I've realized a lot of people online think this is impossible. It's actually right there in the documentation; although, somewhat obscurely tucked away. MySQL DOES allow you to specify what columns to operate on when using LOAD DATA INFILE. Just use the following syntax.

LOAD DATA INFILE '/some/file/name.csv' INTO TABLE SomeTableName FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3);

The field list goes at the end. Adjust the statement according to your needs, and enjoy the 20X increase in data load times.

5) atomic rename

There are a number of situations where you want to replace an entire table worth of data with a new table instantly. This is easily accomplished with an atomic rename and avoids a brief downtime while the tables are being switched.

RENAME TABLE TableName TO TableNamePrevious, TableNameNew TO TableName;

6) monitor a long running insert

If you've ever dealt with long-running inserts, you probably know how annoying it can be to just sit and wonder when the task will complete. For inserts performed in a single transaction, a simple select count(*) won't do because they're all or nothing. Take the following example:

INSERT INTO TableA SELECT * FROM TableB;

Fortunately, there's a way to peak behind the scenes. Do a count on the target table beforehand and make sure it has a populated primary key column. From there, you can do:

SHOW CREATE TABLE TableName;

Look at the last line of the table definition, and you'll see an AUTO_INCREMENT value. That's the last increment of the primary key. If you started with 25,000 rows, and AUTO_INCREMENT is 50,000 then you know 25,000 new rows have been inserted.

7) monitor a LOAD DATA INFILE

If you're using InnoDB, you can do SHOW INNODB and look at the transactions list. From there, "undo entries" displays the number of rows inserted via the current LOAD DATA INFILE operation thus far. This is convenient because I don't believe the AUTO_INCREMENT trick works for LOAD DATA INFILE.