Search code examples
mysqlconcurrencyindexingload-data-infile

MySQL, LOAD DATA CONCURRENT LOCAL, disable & enable keys

I currently insert data into tables with MyISAM engine,

I wish to optimize LOAD DATA by building the indexes after LOAD DATA finishes (disabling keys beforehand and enabling them after it finishes).

From LOAD DATA CONCURRENT documentation, load data should add records to the end of the table, in the same time i am interested that a concurrent read to the same table will continue to use keys. I guaranty that the read operation will try to read info from the table only till the last available index (auto incremented id) before LOAD DATA CONCURRENT starts.

e.g.

LOAD DATA CONCURRENT will add data starting from index 100000 and will auto increment the index till 200000.

in the same time SELECT * FROM TableName WHERE id <=99999 AND ... statement may occur.

Is it possible to disable keys locally only for the LOAD DATA CONCURRENT statement, so that the read operation will still use the defined indexes in its SELECT statement?

Solution

  • I assume that 1. your id column is the primary key, and that 2. you are planning to use ALTER TABLE ... DISABLE KEYS.

    According to the manual:

    ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. (...) While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

    Therefore, the primary key will still be used for queries of the form

    SELECT * FROM TableName WHERE id <=99999

    However, it is not possible to disable keys on a subset of a table only.