Geek Answers Handbook

Rearrange MySQL pause index to bulk INSERT without transaction

I have a lot of data for INSERT LOW_PRIORITY in a table. Since the index is rebuilt every time a row is inserted, it takes a lot of time. I know that I can use transactions, but this is the case when I do not want the whole set to work if one line works.

Is there a way to get MySQL to stop rebuilding indexes on a specific table until I say that it can resume?

Ideally, I would like to insert 1000 rows or so, set the index, make it, and then insert the next 1000 rows.

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

Not important, but I use PHP / PDO to access MySQL. Any advice you could give would be greatly appreciated. Thank!

+2
source share
2 answers

AFAIK, for those using InnoDB tables, if you do not want indexes to be rebuilt after each INSERT, you should use transactions.

For example, to insert a batch of 1000 rows, use the following SQL:

SET autocommit=0;
//Insert the rows one after the other, or using multi values inserts
COMMIT;

By disabling autocommit, the transaction will start from the first INSERT. Then the rows are inserted one after the other and at the end, the transaction is committed and the indices are rebuilt.

INSERT , , , . , , INSERT , INSERT, , , , , .

, INSERT , , . SELECT READ_UNCOMMITTED, , , , SELECT INSERT. . .

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

. , .

multi-inserts (INSERT INTO table (...) VALUES (...), (...), (...), .

+5

More articles:


All Articles