My import of sql via the mysql console is rather slow and as our sql file is increasing every day I would like to know if there are any alternatives on how to import a sql file faster.
Changing to oracle or other systems is no option, the configuration has to stay the same.
Currently the sql file is: 1.5 Gb I'm on Wamp with Apache 2.2.14, PHP 5.2.11 and MySQL 5.1.41.
perhaps the issue is here, import is done by a simple: mysql -u username -p dbname < sqlfilename.sql
Any suggestions?
I'm not sure if this will solve your problem, becuase I'm not sure where the bottleneck is... However, I've have been really impressed with the free Toad for MySQL tool as a replacement MySQL management console. It's got great import tools, and is generally miles better IMO than the standard MySQL management console.
Having indexes enabled during import will slow your server down to a crawl. ALTER TABLEtablenameDISABLE KEYS; and using ..ENABLE KEYS prior to and after import, will improve import speed, but will take some time to re-create indexes, so it might not be a big speed gain after all.
Also, perhaps using myisam tables (in contrast to innodb with referential integrity options) usually gives better performance, as there is no referential integrity overhead involved.
Personally, I don't use import statement from mysql console, but import sql files using mysql -uUSER -pPASS DBNAME < file.sql, and it works well for me.
Hope it helps.