> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Bulk Inserts in MySQL

> How to insert large amounts of data into MySQL efficiently: multi-row INSERT, LOAD DATA INFILE, INSERT...SELECT, disabling keys, and transaction batching for performance.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

Inserting rows one at a time is the fastest way to make a large import slow. MySQL processes each single-row `INSERT` as a full transaction commit — parse, plan, execute, fsync. At scale, that per-row overhead dominates. Here's how to avoid it.

## Multi-Row INSERT

The simplest optimization: batch multiple rows into one statement.

```sql theme={null}
-- Slow: one round trip and one transaction per row
INSERT INTO products (name, price) VALUES ('Widget A', 9.99);
INSERT INTO products (name, price) VALUES ('Widget B', 14.99);
INSERT INTO products (name, price) VALUES ('Widget C', 19.99);

-- Fast: one round trip, one transaction for all three
INSERT INTO products (name, price) VALUES
    ('Widget A',  9.99),
    ('Widget B', 14.99),
    ('Widget C', 19.99);
```

Batch size matters. A few hundred to a few thousand rows per statement is typically optimal — larger batches increase memory pressure and extend transaction duration. Benchmark to find the sweet spot for your workload; 500–2000 rows per batch is a common starting point.

## Transaction Batching

Wrapping many INSERTs in a single transaction avoids per-statement commit overhead:

```sql theme={null}
START TRANSACTION;
INSERT INTO products (name, price) VALUES ('Widget A',  9.99);
INSERT INTO products (name, price) VALUES ('Widget B', 14.99);
-- ... hundreds more ...
INSERT INTO products (name, price) VALUES ('Widget Z', 99.99);
COMMIT;
```

Without explicit transactions, each INSERT auto-commits. Auto-commit means each row is individually flushed to disk. Wrapping 500 inserts in one transaction does one flush.

Combine this with multi-row INSERT: batch rows into groups of 500–1000 per statement, wrap each batch in a transaction.

## LOAD DATA INFILE

For large CSV imports, `LOAD DATA INFILE` is the fastest native MySQL option — significantly faster than batched INSERTs because it bypasses SQL parsing for each row.

```sql theme={null}
LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES  -- skip header row
(name, price, category);
```

The file must be readable by the MySQL server process. By default, the file path is restricted to the directory specified by `secure_file_priv`:

```sql theme={null}
SHOW VARIABLES LIKE 'secure_file_priv';
```

To load a file from the client machine rather than the server:

```sql theme={null}
LOAD DATA LOCAL INFILE '/path/on/client/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
```

`LOCAL INFILE` requires `local_infile = ON` on the server and the client to have the `--local-infile` flag enabled.

## INSERT ... SELECT

For copying data between tables (ETL patterns, staging tables), `INSERT...SELECT` is faster than reading rows into the application and inserting them back:

```sql theme={null}
-- Load staging table, validate, then move to production
INSERT INTO products (name, price, category)
SELECT name, price, category
FROM products_staging
WHERE price > 0 AND name IS NOT NULL;
```

## Disabling Indexes During Bulk Load

For MyISAM tables, disabling non-unique index updates during a bulk insert speeds things up significantly:

```sql theme={null}
ALTER TABLE products DISABLE KEYS;
-- ... bulk INSERT ...
ALTER TABLE products ENABLE KEYS;  -- rebuilds indexes in one pass
```

For InnoDB, this doesn't have the same effect. Instead, InnoDB automatically uses a "change buffer" for secondary index updates. For maximum InnoDB bulk-load performance:

```sql theme={null}
-- Temporarily increase the buffer pool if possible
SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024;  -- 4GB example

-- Disable foreign key checks
SET foreign_key_checks = 0;
-- ... bulk load ...
SET foreign_key_checks = 1;

-- Disable unique checks for known-clean data
SET unique_checks = 0;
-- ... bulk load ...
SET unique_checks = 1;
```

Re-enable these immediately after the load. Disabling `unique_checks` on data that actually has duplicates produces a corrupt index.

## Performance Comparison

| Method | Relative speed | Best for |
| :- | :- | :- |
| Single-row INSERT (auto-commit) | Slowest | Small volumes, interactive |
| Single-row INSERT (batched in transaction) | 2–5× faster | Moderate volumes |
| Multi-row INSERT | 5–20× faster | Most bulk loads |
| LOAD DATA INFILE | Fastest (often 10–100× faster than single-row) | Large CSV imports |
| INSERT...SELECT | Fast (server-side copy) | Table-to-table copies |

Exact numbers depend heavily on row size, indexes, hardware, and configuration. Benchmark your specific case.

## Frequently Asked Questions

#### How large should each INSERT batch be?

Start at 500–1000 rows and benchmark. Larger batches reduce per-commit overhead but increase memory usage and transaction duration (which holds locks longer). If a batch fails, the whole batch rolls back — smaller batches mean less rollback work. Most applications find the optimal batch size between 500 and 5000 rows.

#### Can I use LOAD DATA INFILE with ON DUPLICATE KEY UPDATE?

`LOAD DATA` supports `REPLACE` or `IGNORE` for duplicate handling, but not `ON DUPLICATE KEY UPDATE`. For upsert semantics during bulk load, load into a staging table, then use `INSERT...SELECT...ON DUPLICATE KEY UPDATE` from the staging table to the target.

#### Does bulk inserting bypass triggers?

No. Triggers fire for every row affected, including those loaded by `LOAD DATA INFILE` and `INSERT...SELECT`. If your trigger does significant work, it will slow bulk loads proportionally. For pure data migrations, you may want to temporarily disable triggers.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `ERROR 1290: The MySQL server is running with the --secure-file-priv option` | Move the file to the directory shown by `SHOW VARIABLES LIKE 'secure_file_priv'`, or use `LOAD DATA LOCAL INFILE` |
| `ERROR 3948: Loading local data is disabled` | Enable with `SET GLOBAL local_infile = 1` on server and `--local-infile=1` on client |
| Bulk insert still slow after batching | Check if `autocommit` is ON — each statement commits individually; wrap in explicit `BEGIN...COMMIT` blocks |
| Duplicate key errors on known-clean data after disabling `unique_checks` | `unique_checks = 0` does not validate uniqueness during load but corrupts the index if duplicates exist — always validate first |
| `LOAD DATA` misparses rows | Check `FIELDS TERMINATED BY`, `ENCLOSED BY`, and `LINES TERMINATED BY` match your actual file format; use `SHOW WARNINGS` after the load |

These loading techniques work unchanged on [VillageSQL](https://villagesql.com/install), a drop-in replacement for MySQL with extensions.

## See also

* [Transactions in MySQL](https://villagesql.com/docs/en/guides/transactions.md) — batching bulk inserts in transactions for safety and speed
* [Schema Migrations in MySQL](https://villagesql.com/docs/en/guides/schema-migrations.md) — bulk inserts are often part of data migration work
* [UPSERT in MySQL](https://villagesql.com/docs/en/guides/upsert.md) — handling duplicate keys during a load
* [MySQL insert multiple rows](https://villagesql.com/docs/en/tutorial/insert-multiple-rows.md) — the multi-row statement itself
* [MySQL INSERT SELECT](https://villagesql.com/docs/en/tutorial/insert-select.md) — copying rows a query returns


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.