Bulk Loading and Maintenance

Article · 3:00

LOAD DATA INFILE for speed, and the three maintenance statements worth knowing.

LOAD DATA INFILE reads a text file straight into a table and is far faster than inserting the same rows one at a time - often by an order of magnitude - because it bypasses per-statement parsing and writes in bulk. For a large import it is the difference between minutes and hours.

Two variants exist and the distinction causes trouble. LOAD DATA INFILE reads a file on the server, restricted to the directory in secure_file_priv. LOAD DATA LOCAL INFILE reads a file on the client and sends it - which requires local_infile enabled on both sides, and is disabled by default because a malicious server can use it to request files from a connecting client.

For maintenance there are three statements. ANALYZE TABLE recalculates the statistics the optimiser uses to choose a plan; run it after a large change in the data, because a plan chosen from stale statistics can be badly wrong. OPTIMIZE TABLE rebuilds the table to reclaim space after large deletes - deleting rows frees space for reuse but does not return it to the filesystem - and it rewrites the whole table, so it is an off-peak job. CHECK TABLE looks for corruption.

Faster imports also come from doing less work per row: disable the unique check and foreign key check for the load, and re-enable them afterwards. Only on an empty or isolated table, and only when you are prepared for the data to be wrong if the file is.

-- Where the server is permitted to read files from. NULL means the
-- feature is off entirely.
SELECT @@secure_file_priv, @@local_infile;

-- Server-side: fast, and the file must live in secure_file_priv.
LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS                       -- the header
(name, email, city, @raw_joined)
SET joined_on = STR_TO_DATE(@raw_joined, '%d/%m/%Y');

-- Client-side: needs local_infile on BOTH sides, and is off by default
-- because a malicious server can use it to read a client's files.
-- LOAD DATA LOCAL INFILE './customers.csv' INTO TABLE customers ...;

-- Less work per row, for a bulk load into an isolated table.
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- ... the load ...
SET unique_checks = 1;
SET foreign_key_checks = 1;

-- Maintenance. ANALYZE after a large data change: a plan chosen from
-- stale statistics can be badly wrong.
ANALYZE TABLE orders;

-- Reclaim space after large deletes. Rewrites the table - off-peak only.
OPTIMIZE TABLE audit_log;

CHECK TABLE orders;

-- How much space a delete has left behind and not returned.
SELECT table_name,
       ROUND(data_length  / 1024 / 1024) AS data_mb,
       ROUND(data_free    / 1024 / 1024) AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE() AND data_free > 0;