Databases Reference
In-Depth Information
That works well if you don't have much data, but if you do, it's often more efficient to
populate the table incrementally, committing the transaction between each chunk so
the undo logs don't grow huge. Assuming that
id
is the primary key, run this query
repeatedly (using larger values of
x
and
y
each time) until you've copied all the data to
the new table:
mysql>
START TRANSACTION;
mysql>
INSERT INTO innodb_table SELECT * FROM myisam_table
->
WHERE id BETWEEN x AND y;
mysql>
COMMIT;
After doing so, you'll be left with the original table, which you can drop when you're
done with it, and the new table, which is now fully populated. Be careful to lock the
original table if needed to prevent getting an inconsistent copy of the data!
Tools such as Percona Toolkit's
pt-online-schema-change
(based on Facebook's online
schema change technique) can remove the error-prone and tedious manual work from
schema changes.
A MySQL Timeline
It is helpful to understand MySQL's version history as a frame of reference when
choosing which version of the server you want to run. Plus, it's kind of fun for old-
timers to remember what it used to be like in the good old days!
Version 3.23 (2001)
This release of MySQL is generally regarded as the moment MySQL “arrived” and
became a viable option for widespread use. MySQL was still not much more than
a query language over flat files, but MyISAM was introduced to replace ISAM, an
older and much more limited storage engine. InnoDB was available, but was not
shipped in the standard binary distribution because it was so new. If you wanted
to use InnoDB, you had to compile the server yourself with support for it. Version
3.23 introduced full-text indexing and replication. Replication was to become the
killer feature that propelled MySQL to fame as the database that powered much
of the Internet.
Version 4.0 (2003)
New syntax features appeared, such as support for
UNION
and multi-table
DELETE
statements. Replication was rewritten to use two threads on the replica, instead of
one thread that did all the work and suffered from task switching. InnoDB was
shipped as a standard part of the server, with its full feature set: row-level locking,
foreign keys, and so on. The query cache was introduced in version 4.0 (and hasn't
changed much since then). Support for SSL connections was also introduced.
Version 4.1 (2005)
More query syntax features were introduced, including subqueries and
INSERT ON
DUPLICATE KEY UPDATE
. The UTF-8 character set was supported. There was a new
binary protocol and prepared statement support.
