Import of SQL-Files is very slow
there is a performance problem: I use heidisql in the latest version, mysql 5 and windows vista. Now i have a *.sql file with 60 tabes and 25000 datasets i want to import. The import is very slow so round about 10 datasets per second. Are there any options to get a better performance? I tried to switch off the firewall and the virus-scanner, but it was not successful :-(
thanks for your help
I know this is 2 years old issue ... but I think this hasn't been solved :(
CREATE TABLE `db` (
`ip` TINYTEXT NOT NULL,
`date` DATE NOT NULL,
`text1` TEXT NOT NULL,
`text2` TEXT NULL,
`hash` VARCHAR(32) NULL DEFAULT NULL,
UNIQUE INDEX `hash` (`hash`)
and inserting 100 000 lines like this
INSERT IGNORE INTO `db` VALUES('220.127.116.11', '2010-01-01', 'wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwwwwwwwww', 'wwwwwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww', '11111111111111111111111111111111');
And now comparation
HeidiSQL ... 5000 lines 2 minutes
phpMyAdmin ... 100 000 lines 1 minute
HeidiSQL executes a mysql_ping() before executing a query, to ensure the connection is alive. Probably that is decreasing also performance.
Anyway. Do you have the chance to recreate that SQL file? If yes, just try it using HeidiSQL (mysqldump is also very ok). The problem with such dumps is that they contain only one single VALUES clause, so they produce 100,000 times the overhead of one larger, extended INSERT, which concatenates many VALUES clauses into one query. You will see that dramatically speeds up reading/executing these dumps from some minutes to some seconds!
Now HeidiSQL is frozen for 1 minute, then it reports that 10 000 lines inserted in 0,5sec ;)
Still, you can make it for example optionable to execute the mysql_ping. I know that it is possible that the server can shutdown suddenly - many people will take the risk :D
Please login to leave a reply, or register at first.