• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
The Awesomeness of Transactions

#1
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?
Reply

#2
(03-10-2017, 04:06 PM)skunkbad Wrote:
Code:
cat ~/Desktop/big-table.sql | mysql -u mysqlUser -p myBigTablesDB

Slightly simpler:
Code:
mysql -uuser -p dbname < dumpfile.sql

(03-10-2017, 04:06 PM)skunkbad Wrote: 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?

A single commit will always be faster than 42k commits. Smile

Transactions are certainly very underrated. Especially by MySQL users who started out with the MyISAM engine, which doesn't support them.

Not a magic bullet of course, but should be used way more than just when you know 2+ consecutive queries must not fail.
Reply

#3
Very interesting thread. So where can I learn more about mySQL transaction. I always want to know how to improve the performance of mySQL actions.

P/S: just found one. https://www.codeigniter.com/user_guide/d...tions.html
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2017 MyBB Group.