Yesterday I had to move some database data from one server to my dev machine. 42000 rows to be specific. So I fired up SQLyog, and I knew I could use the built in transfer tool, but due to laziness just downloaded a data dump of the table. I tried to run the dump file through MySQL in the terminal:
Code:
cat ~/Desktop/big-table.sql | mysql -u mysqlUser -p myBigTablesDB
An hour later, and it's still not done....
So I stopped it, and tried it again using transactions:
Code:
sed -i "1s/^/TRUNCATE TABLE \`big-table\`;\nSTART TRANSACTION;\n /" ~/Desktop/big-table.sql && echo "COMMIT;" >> ~/Desktop/big-table.sql
cat ~/Desktop/big-table.sql | mysql -u mysqlUser -p myBigTablesDB
First command ran instantly. Second command ran in
8 seconds, inserting all 42000 rows.
The difference in execution time was amazing. I guess I always thought transactions were to be used in case something goes wrong, and that way the tables can be rolled back to where they were before the queries. It's obvious they are useful in other situations.
Anything more to say about transactions?
