Would having x number of UPDATE queries in a MySQL transaction be worse or about the same as putting them into one UPDATE query?

Question

This may be a silly question with an obvious answer, but I've pondered it for quite some time and can't really come up with a good answer on my own.

I'm working on a telecommunications website and I've made a mapper framework and query builder of which I'm rather proud. The mapper has a set function (generates an UPDATE query) to update each column.

One downside to this method is I can't wrap multiple updates into one nice query, each update has it's own query. As of right now, I have the option available to start and commit a transaction built into the mapper classes.

Would using transactions be less taxing on the database server when compared to doing each query individually? I know doing it as one query would probably still be better...

(If this wasn't all that clear, let me know. I'll be happy to rephrase.)

-- Logan

Answer 1

Running a single query is always faster than running multiple queries. And regarding transactions, not all table engines support transactions, so you have to have a look at that too. And even if you send multiple updates within a transaction, its again going to have an overhead as compared to a single query. Besides that whenever possible its always a good choice to have a single query do the work (MySQL usually would optimize your query by applying various optimization alogs).