CODEWITHSUNDEEP
mysqlperformance
Ognjen Mišić
Ognjen Mišić

Reputation: 1416

MySQL UPDATE performance with or without WHERE IS NULL

Lets say I have 300 million users in my mysql database (innodb). Some of them have username set, while some of them don't (username is null), and lets say 60% of them are not null (have actual varchar value).

If I wanted to set all 300 million users' usernames to null, would

UPDATE users SET username = null WHERE username IS NOT NULL

perform better than

UPDATE users SET username = null - without a WHERE clause, just blanket null them all?

I know that WHERE always performs faster when setting actual values, but somehow null fields made me think about this.

Upvotes: 0

Views: 210

Answers (1)

Rick James
Rick James

Reputation: 142298

Both will take terrrrribly long. I suggest you do it in 'chunks' as described in my blog here: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

Here is another solution:

ALTER TABLE t DROP COLUMN c;
ALTER TABLE t ADD COLUMN c VARCHAR(...) DEFAULT NULL;

Each ALTER will copy the table over once without writing to the ROLLBACK log (etc), thereby being significantly faster. (I doubt if you can combine the two into a single statement.)

But first, let's back up and discuss why you need to do this unusual task. It is likely to indicate a poor schema design. And rethinking the design may be a better approach.

Upvotes: 1

Related Questions