Databases Reference
In-Depth Information
uniqueness as it loads each row. Loading becomes extremely slow as soon as the
index's size exceeds the available memory.
In modern versions of InnoDB, you can use an analogous technique that relies on
InnoDB's fast online index creation capabilities. This calls for dropping all of the non-
unique indexes, adding the new column, and then adding back the indexes you drop-
ped. Percona Server supports doing this automatically.
As with the
ALTER TABLE
hacks in the previous section, you can speed up this process
if you're willing to do a little more work and assume some risk. This can be useful for
loading data from backups, for example, when you already know all the data is valid
and there's no need for uniqueness checks.
Again, this is an undocumented, unsupported technique. Use it at your
own risk, and back up your data first.
Here are the steps you'll need to take:
1. Create a table of the desired structure, but without any indexes.
2. Load the data into the table to build the
.MYD
file.
3. Create another empty table with the desired structure, this time including the in-
dexes. This will create the
.frm
and
.MYI
files you need.
4. Flush the tables with a read lock.
5. Rename the second table's
.frm
and
.MYI
files, so MySQL uses them for the first
table.
6. Release the read lock.
7. Use
REPAIR TABLE
to build the table's indexes. This will build all indexes by sorting,
including the unique indexes.
This procedure can be much faster for very large tables.
