Autocommit vs. Single Transactions (Small Project vs. Big Project)

Question

Hi there,

I am currently working on a very specialized PHP framework, which might have to handle large database transfers.

For example: Take half of the whole user count; this should be every day's workspace for the framework.

So, if my framework is required by big projects, is it recommend to use single transactions with multiple queries (e.g. doing many things in 1 query with JOINs(?)), or is auto-commit preferred?

If possible, post some blog entries which have discussed this problem.

Thank you. ;)

Answer 1

MyISAM is transactionless, so autocommit does not affect it.

As for InnoDB, autocommit makes repeating queries hundreds of times as slow.

The best decision is of course doing everything set-based, but if you have to do queries in a loop, turn autocommit off.

Also note that "multiple queries" and "doing many things in one query with JOIN" are completely different things.

The latter is an atomic operation which succeeds or fails at once even on MyISAM.

Autocommit does not affect its performance as such: everything inside this query is always done in a single transaction.

Doing many things in one query is actually a preferred way to do many things.

Answer 2

Joins are not just useful they are necessary in any but the simplest of datastructures. To consider writing without them shows a fundamental lack of understanding of relational database design and access to me. Not to use joins would usually result in getting the wrong answer to your question in a select. You may get away without joins in inserts, updates and deletes, but they are often used and useful there as well.