CODEWITHSUNDEEP
mysqlxmlperformance
user1987800
user1987800

Reputation: 21

how to speed data loading XML file into MySQL

I've got a 2Gb XML file that I want to load into a single table in MySQL.

The number of records/rows is ~140,000, but the default behavior of the LOAD XML function in MYSQL seems to depart from linear time.

Cutting the data into smaller pieces, I get the following performance (dropped table between each LOAD)

all were: Deleted: 0 Skipped: 0 Warnings: 0

5000 row(s) affected Records: 5000 4.852 sec

10000 row(s) affected Records: 10000 20.670 sec

15000 row(s) affected Records: 15000 80.294 sec

20000 row(s) affected Records: 20000 202.474 sec

The XML is well formed. I've tried: SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;

What can I do to load it in a reasonable time that doesn't involve cutting it into a dozen pieces?

Upvotes: 2

Views: 551

Answers (1)

xpda
xpda

Reputation: 15813

Try removing the indexes before the load, then rebuilding them afterward.

Upvotes: 1

Related Questions