I have a script, which populates 4 tables. It goes like this:

  1. Disable the keys on all the tables
  2. Populate the tables
  3. Enabled the keys back.

The problem is that windows decided to reboot the machine at night while it was at the step 3. I did repair on all the tables, but I have no idea whether the keys are enabled or not.

I do not want to enable it blindly, because it takes hours.

How do I check it?


For this example

  • assume that all the tables are being loaded in the same database.
  • let all tables be in the database called mydb

First, collect all table names in mydb that are to be bulk loaded (one time setup)

DROP TABLE IF EXISTS mydb.TablesToBulkLoad;
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'mydb'
AND table_name NOT IN ('TablesToBulkLoad','TablesLeftToEnableKeys');
ALTER TABLE mydb.TablesToBulkLoad ADD PRIMARY KEY (table_name);

For your on-demand bulk load, setup a table that tracks who has been loaded

Change the style of your script to do the following

Step01 : DISABLE KEYS on all tables

Step02 : Populate the tables

Step03 : Collect list of tables to process by doing the following:

DROP TABLE IF EXISTS mydb.TablesLeftToEnableKeys;
CREATE TABLE mydb.TablesLeftToEnableKeys LIKE mydb.TablesToBulkLoad;
INSERT INTO mydb.TablesLeftToEnableKeys SELECT * FROM mydb.TablesToBulkLoad;

In a separate script, load every table_name in mydb.TablesLeftToEnableKeys to bulk ENABLE KEYS

Step04 : For each table_name in Step03, do the ENABLE KEYS

mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',table_name,' ENABLE KEYS; DELETE FROM mydb.TablesLeftToEnableKeys WHERE table_name = ',table_name,';') FROM mydb.TablesLeftToEnableKeys" > /root/BulkEnableKeys.sql
echo "DROP TABLE IF EXISTS mydb.TablesLeftToEnableKeys;" >> /root/BulkEnableKeys.sql
mysql -uroot < /root/BulkEnableKeys.sql

What is so special about doing this? After Step03, you have a list of tables that need to have ALTER TABLE ... ENABLE KEYS executed.

In the event of a crash, just rerun Step04 in that separate script because it will remember which tables did not execute or complete the execution of ENABLE KEYS

Give it a Try !!!

UPDATE 2012-03-15 16:04 EDT

Unfortunately, there does not seem to be any API or direct SQL in MySQL to accommodate checking to see if there is a state or flag within the MyISAM table to detect if the keys for a MyISAM are in a disabled state. I am sure some MySQL Internals developer has that answer. Oh that's right. Oracle own MySQL now. Have fun looking for a developer.

If you are interesting in an ugly, but workable, method to speed ENABLE KEYS, there is something you can do with my answer.

For each table you plan to populate do the following (for this example, let's use the MyISAM mydb.mytable):

  • In mysql, CREATE TABLE mydb.mytable_copy LIKE mydb.mytable;
  • In mysql, CREATE TABLE mydb.mytable DISABLE KEYS;
  • Populate mydb.mytable (whether you add more rows or replace all rows)
  • In Linux, run these commands

Linux commands

cd /var/lib/mysql/mydb
rm -f mytable.frm
rm -f mytable.MYI
cp mytable_copy.frm mytable.frm
cp mytable_copy.MYI mytable.MYI
  • In mysql, FLUSH TABLES;
  • In mysql, REPAIR TABLE mydb.mytable;

This should be faster than ENABLE KEYS;

I got this idea from an internal letter from MySQL

| improve this answer | |
  • This is very interesting, yet it does not answer my question - how do I know now which tables have the keys enabled? – mark Mar 15 '12 at 18:42
  • Updating my answer... – RolandoMySQLDBA Mar 15 '12 at 20:03

Just run an EXPLAIN on a SELECT statement that would normally use a key. If the keys are disabled, then the columns possible_keys, key, key_len, ref will return null.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.