MySQL: Alter Table Disable Keys

MySQL Disable Indexes Command

When inserting,updating or deleting a high percenblege of the data from a table, it is a best pratce to disable indexes. Especaily, when doing a bulk load into a empty table.  To disable indexes, one can drop all the index keys and recreate them later. Or, we can use Alter Table table name Disable Keys to deactivate  indexes  on a MyISAM table.

To do that, before performing the sql statements that involve with insert, update or delete, issue the following command:

         mysql> alter table tablename disable keys;

Now perform the tasks; for example:

        mysql> insert into tablename(...) values( ...);

After the task has been completed, issue the following command to reactivate the indexes:

         mysql>alter table tablename enable keys;

ALTER TABLE tablename DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE tablename ENABLE KEYS tells MySQL to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. When using ALTER TABLE tablename DISABLE KEYS, please keep in mind the following:

•  ALTER TABLE tablename DISABLE KEYS will keep UNIQUE keys active.
• Using ALTER TABLE tablename DISABLE KEYS requires ALTER, INSERT, CREATE and the INDEX privilege for that table.
• While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.
• ENABLE KEYS and DISABLE KEYS were not supported for partitioned tables prior to MySQL 5.1.11.

 References:

• MySQL Manual: ALTER TABLE Syntax.
• Other References On The Web. 

MySQ: Delay_Key_Write

MySQL Alter Table t Delay_ Key_ Write Command

The delay_key_write option works with MyISAM tables. Delayed key writing tells storage engine not to wtire change index data to disk at the end of any query that changes the data. Instead, the changes are kept in the key buffer until the server prunes the buffer or close the table. This option can boots performance if the MyISAM table is changing frequenry, for example, has a lot of inserts and updates.

To turn on delay_key_write option for a table, you can include the following statement in to create table script:

     Delay_key_write =1

Or for existing table, use the alter table statement:

    MySQL> ALTER TABLE table name DELAY_KEY_WRITE = 1;

Please note that,in MySQL, Alter Table statement takes time to excute and locks the table. Therefore, for large tables, so do this with care.

There are a few thing need to keep in mind when using delay_key_write option as well. First, the changes, mainly, the index changes, will be kep in memory until the buffer cache prunes or the table is closed. A table is closed when:

•   When the cache is full and a thread tries to open a table that is not in the cache.
•  When the cache contains more than table_cache entries and a thread is no longer using a table.
•  When FLUSH TABLES command is issued.

 This means that if the server or table is crashed, there will be indexes cruptions since the indexes on the index file (tablename.MYI) was not updated while the data file (tablename.MYD) was. Therefore, it is recomanded that the myisam-recover variable to be turned on during server startup. For example, set
 --myisam-recover=BACKUP,FORCE in my.cnf file or start the server with –myisam-recover option. Secondly, if the table has many write delays, it will table longer for the table to close since it needs time to flush all the caches to the disk, and it will take longer to run FLUSH TABLES statements(flush table, flush table with read lock) Hence, flush tables should be used a bit frequely if you use delay_key_write option. Lastly, queries may need to wait for the MyISAM engine to free up space in the key buffer cache because the unflushed dirty blocks in the key buffer cache take up space.

Delay_Key_write option can also be enable globally for all MyISAM tables. To do so, set the delay_key_write option to ‘all’:

   delay_key_write = all

However, it is not a commend pratics to most systems because for infrequently written tables, delay_key_write may increase the chance of index corruption.

 References:

• MySQL Manual: How MySQL Opens and Closes Tables
• High Performance MySQL: Chapter 1, MySQL Architecture. Chapter 6, Optimizing Server Settings
• Other References On The Web

MySQL: Failed to CREATE PROCEDURE ERROR 1307 (HY000)

ERROR 1307 (HY000): Failed to CREATE PROCEDURE …

When tried to create a procedure, I got the above error message. The user had the needed create routine privileges and the procedure syntax was corrected. Used the following command to check the version and found out that it was v.5.1.30.

mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.30-community-log |
+----------------------+
1 row in set (0.00 sec)

After some researches, it turned out that the database was a copy from an earlier version. So to resolve the problem, I need to apply the mysql_upgrade script. For Redhat Linux installation, it was located in /usr/bin. But it is easy to find out where it is by using the Linux ‘which’ command:

#> which mysql_upgrade
/usr/bin/mysql_upgrade

To excute the script, from the command line run the script using mysql root user and password:

#>/usr./bin/mysql_upgrade -u root –p

Now it should be fine. The script also printed out messages on the screen on what got updated during the execution.
References:

• MySQL Manual: Upgrading from MySQL 5.0 to 5.1
• Other References On The Web