Welcome Guest, Not a member yet? Register   Sign In
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

(This post was last modified: 03-10-2017, 03:39 PM by Narf.)

(03-10-2017, 03: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, 03: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

(This post was last modified: 03-12-2017, 09:59 AM by AzrielOmega.)

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




Theme © iAndrew 2016 - Forum software by © MyBB