ansaurus

Question

mysql UPDATE statement - overhead for same values?

Answer 1

+1 A:

Why not just use

UPDATE  dbo.Authors
SET     AuthorType = 10
WHERE   AuthorType <> 10

and have the best of both worlds.

Note that a few hundred thousands records should pose no problem for any modern database engine.

Lieven 2010-07-06 14:20:56

This is faster than doing 2 queries, and should be faster than updating records that didn't need to be updated.

MJB 2010-07-06 14:23:20

This is extra trouble, and when you later add a field to your update (eg you want to set AuthorDeleted = 0) you have to change your WHERE-clause as well. You gain nothing, but increase complexity. Don't do this.

Konerak 2010-07-06 14:24:43

@Konerak, you have got to be joking?! I would be *very* hard pressed where using a WHERE clause would be slower. Also please note that downvotes should be preserved for answers that are completely wrong, not as a tool to favour your own answer and game the system.

Lieven 2010-07-06 14:31:56

Woah, I didn't say it would be slower? I said you increase complexity in your query and make it less maintainable? Unless you can show me where it actually performs faster (maybe with PARTITIONING? I haven't tested that...)

Konerak 2010-07-06 14:37:26

OP requested the most efficient solution. He now has the impression that not using a where clause is the most efficient solution. A WHERE clause *might* use an index to do the update. That would always outperform a blank update.

Lieven 2010-07-06 14:42:36

Answer 2

+3 A:

No, MySQL is smart and won't be slower. Don't go through the trouble of checking for that, MySQL will do it for you.

If you set a column to the value it currently has, MySQL notices this and does not update it. No write action is performed. (Source)

Konerak 2010-07-06 14:22:38

@JiminyCricket - beware that this might be misleading as to your actual question. A *seperate* query will most likely be slower. What I believe Konerak is telling you is that not using a where clause is not slower. I disagree.

Lieven 2010-07-06 14:39:10

In principle a `WHERE` could be faster not because it short-circuits any writes, but because it could use an index to narrow down the number of rows to look at first. In practice a not-equal comparison like `authortype<>10` is not going to benefit from indexing.

bobince 2010-07-06 15:11:16

@bobince, kind of what I replied in the comments to my own answer. It would be safe to say that using a `WHERE` clause will not be slower but *might* be faster. Hence, why not always use it and save you the trouble having to look up the specific circumstances where it doesn't matter.

Lieven 2010-07-06 15:31:36

Answer 3

A:

If your table contains hundreds of thousands of rows, it isn't worth worrying about how long it will take. The time taken to decide will be much larger than the time to just do it.

Had you hundreds of millions of rows, it might be a different story.

Brian Hooper 2010-07-06 14:25:49

ansaurus

tags:

views:

answers:

mysql UPDATE statement - overhead for same values?

related questions