Updating multiple rows in one query very slow performance

I am looking at the best way to update multiple rows at once with a single query. Currently I have:

UPDATE `profiles` SET `name` = CASE `id` WHEN 1 THEN 'John' WHEN 2 THEN 'Jane' END, `gender` = CASE `id` WHEN 1 THEN 'Male开发者_JAVA百科' WHEN 2 THEN 'Female' END WHERE `id`=1 OR `id`=2

but this takes about 4 minutes to complete (my real query is on 10 fields in a database of 20 million rows) as opposed to individual update queries that take about 1 second.

I am trying to work out why, what is actually happening? I thought that by specifying the id in the WHERE clause that this would speed it up.

---

Could you post the DDL for the profiles table, please? This will help to see what kind of indexes you have set up (for example - can we assume that the id column is the primary key here?). If you're using MySQL then just run 'SHOW CREATE TABLE profiles' to generate the DDL.

A couple of points that might help out:

1) Try using a BETWEEN in your WHERE clause instead of an OR. e.g. UPDATE profiles

SET `name` = 

CASE `id` WHEN 1 THEN 'John' 

WHEN 2 THEN 'Jane' END, 

`gender` = CASE `id` 

WHEN 1 THEN 'Male' WHEN 2 THEN 'Female' END 

WHERE `id` between 1 and 2;

2) Try splitting the query in separate queries to avoid using the CASE statement e.g.

update `profiles`

set `name` = 'John',

    `gender` = 'Male'

where `id` = 1;

and

update `profiles`

set `name` = 'Jane',

    `gender` = 'Female'

where `id` = 2;

I don't know if this is feasible since I'm not sure in what context you are using the query! Hope that helps.

---

Can you please specify all your case for all fields, so we have better idea. If you have fix case to update only for id=1 and 2 then split your query in 2 queries like :

update `profiles` set `name` = 'John', `gender` = 'Male' where `id` = 1; 
update `profiles` set `name` = 'Jane', `gender` = 'Female' where `id` = 2; 

---

Do you have an index on id? If not, it's a good idea to create one (Warning, this can take a long time, do this in off-peak hours):

CREATE INDEX id_idx ON profiles (id);

By the way, a query on 10 fields with 20 million rows in a table can take long, especially if there are not indices or the cache is cold.

Update: For testing and because I was curious I tried to reproduce your situation. For this I made up some test-data.

DDL: https://gist.github.com/b76ab1c1a9d0ea071965
Update Query: https://gist.github.com/a8841731cb9aa5d8aa26
Perl script to populate the table with test-data: https://gist.github.com/958de0d848c01090cb9d

However, as I already mentioned in my comment below, Mysql will prevent you from inserting duplicate data because id is your PRIMARY KEY, but not unique. If you could comment on the table schema and/or post your DDL, this would help a lot.

Good luck! Alex.