Bookmark and Share
Building MyISAM Indexes Quickly
(Publish Date: 2009-9-22 11:11am, Total Visits: 2171, Today: 1, This Week: 1, This Month: 2)

The usual trick for loading MyISAM tables efficiently is to disable keys, load the data, and reenable the keys:

mysql> ALTER TABLE test.load_data DISABLE KEYS;

-- load the data

mysql> ALTER TABLE test.load_data ENABLE KEYS;

This works because it lets MyISAM delay building the keys until all the data is loaded, at which point, it can build the indexes by sorting. This is much faster and results in a defragmented, compact index tree.

Unfortunately, it doesn’t work for unique indexes, because DISABLE KEYS applies only to nonunique indexes. MyISAM builds unique indexes in memory and checks the uniqueness as it loads each row. Loading becomes extremely slow as soon as the index’s size exceeds the available memory.

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.

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
indexes. 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.

Extracted from

 

 


[Total Users: 1]

I want to comment on it

Display: 1 - 0 of 1, Total Pages: 0