MySQL pause index rebuild on bulk INSERT without TRANSACTION

Question

I have a lot of data to INSERT LOW_PRIORITY into a table. As the index is rebuilt every time a row is inserted, this takes a long time. I know I could use transactions, but this is a case where I don't want the whole set to fail if just one row fails.

Is there any way to get MySQL to stop rebuilding indices on a specific table until I tell it that it can resume?

Ideally, I would like to insert 1,000 rows or so, set the index do its thing, and then insert the next 1,000 rows.

I cannot use INSERT DELAYED as my table type is InnoDB. Otherwise, INSERT DELAYED would be perfect for me.

Not that it matters, but I am using PHP/PDO to access MySQL. Any advice you could give would be appreciated. Thanks!

Answer 1

ALTER TABLE tableName DISABLE KEYS
// perform inserts
ALTER TABLE tableName ENABLE KEYS

This disables updating of all non-unique indexes. The disadvantage is that those indexes won't be used for select queries as well.

You can however use multi-inserts (INSERT INTO table(...) VALUES(...),(...),(...) which will also update indexes in batches.