MySQL UPDATE statement performance

Question

On a table with over 10 million rows, which one of these UPDATE statements likely to faster, or are they much of a muchness?

UPDATE table SET col1 = col2 WHERE col1 IS NULL;
UPDATE table SET col1 = col2 WHERE col1 IS NULL AND col2 IS NOT NULL;

Neither col1 nor col2 are keys or indexed. They are actually datetime fields, if that changes anything.

Note: I'm fairly sure that the queries achieve the same result:

Query 1. will update col1 every time, including setting it to NULL if col2 is NULL.

Query 2. will update col1 only if col2 has a value, thereby leaving col1 as NULL if col2 is NULL.

They both change the data in the same way. One sets col1 to NULL when col2 is NULL, the other leaves col1 as NULL when col2 is NULL, but in all cases col1 was NULL to begin with.

two query are different. Both query will not update same rows. — Suresh Kamrushi
– Suresh Kamrushi, Commented Nov 27, 2013 at 10:05
As @SureshKamrushi says, they are not the same, logically - but also observation says that the 1st would be faster as it is performing less checks - though the actual performance difference may well be negligible. Have you tried profiling them? — Moo-Juice
– Moo-Juice, Commented Nov 27, 2013 at 10:06
You should also take into account how many of the 10 Million have col2 as NULL. If you only have 5 that have col2 as not null, then the second one should be faster as it has not to do almost 10 Million updates. — Filipe Silva
– Filipe Silva, Commented Nov 27, 2013 at 10:08
Analyzing this query, maybe the performance will depends on the content of the table. If you have many NULL in col1 and less col2 with values, there are less to update. Meaning, col1 and col1 values is inversely proportional with respect to performance. — Mark
– Mark, Commented Nov 27, 2013 at 10:09

Zsolt Szilagyi · Accepted Answer · 2013-11-27 10:25:08Z

1

Performance is better measured than guessed. Yet here is my guess: It heavily depends on your usage of indeces, and their cardinality.

That means: if col2 has an index with thousands of different values and null beeing one of them, it´ll be faster than query 1. If there is no query and col2 almost always is null, it will be slower.

Best performance will be achieved if you have a two-column-index spanning both cols, and use both cols in the where-part.

answered Nov 27, 2013 at 10:25

Zsolt Szilagyi

5,0465 gold badges31 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stefan Winkler · Accepted Answer · 2013-11-27 10:20:44Z

0

It actually depends on different factors.

UPDATE table SET col1 = col2;

Will do a full table scan and modification in any case.

These queries

UPDATE table SET col1 = col2 WHERE col1 IS NULL;
UPDATE table SET col1 = col2 WHERE col1 IS NULL AND col2 IS NOT NULL;

will result in faster operation, if col1 and col2 are indexed.

You can get information about what is done in which case if you execute

EXPLAIN UPDATE table SET col1 = col2;
EXPLAIN UPDATE table SET col1 = col2 WHERE col1 IS NULL;
EXPLAIN UPDATE table SET col1 = col2 WHERE col1 IS NULL AND col2 IS NOT NULL;

answered Nov 27, 2013 at 10:20

Stefan Winkler

3,9761 gold badge21 silver badges36 bronze badges

1 Comment

Stewart Over a year ago

Just tried this. Turns out MySQL doesn't let you use EXPLAIN with UPDATE statements I'm afraid.

Philip Sheard · Accepted Answer · 2013-11-27 10:29:30Z

0

In my experience the second format is usually preferable, but of course it will depend on your data. It is important to note that indexes on the columns in question may not include null values, depending on the database provider and the way that the indexes are defined. Even if indexes are defined, they may not improve performance, and depending on your statistics they may not be used at all.

answered Nov 27, 2013 at 10:29

Philip Sheard

5,8335 gold badges30 silver badges42 bronze badges

Comments

Stewart · Accepted Answer · 2013-11-27 17:37:14Z

0

In response to the various comments & answers about measuring rather than guessing, and also to the comments regarding the count of rows where col2 is NULL vs col2 having a value of some kind, I ran 4 tests on a test table:

                            | Query 1          | Query 2
 col2 almost all NULL       |  5 min 27.96 sec |  4 min 20.38 sec
 col2 almost all has values | 34 min 31.64 sec | 32 min 31.54 sec

Conclusion: Query 2 is marginally faster in both cases.

answered Nov 27, 2013 at 17:37

Stewart

18.9k10 gold badges60 silver badges90 bronze badges

Collectives™ on Stack Overflow

MySQL UPDATE statement performance

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related