Updating multiple counters in one query

Question

My application currently does something like the following (minimal example):

UPDATE counts SET count = count + 1 WHERE id = 1
UPDATE counts SET count = count + 1 WHERE id = 2
UPDATE counts SET count = count + 1 WHERE id = 2
UPDATE counts SET count = count + 1 WHERE id = 3
UPDATE counts SET count = count + 1 WHERE id = 3

However there are a few thousand updates per second, and at peak traffic it it too slow.

So I'd like to speed it up by combining the statements. The following of course does not work:

UPDATE counts SET count = count + 1 WHERE id IN (1, 2, 2, 3, 3)

The counts of 2 and 3 would be wrong (1 instead of 2).

Do I manually have to split it into something like the following:

UPDATE counts SET count = count + 1 WHERE id IN (1, 2, 3)
UPDATE counts SET count = count + 1 WHERE id IN (2, 3)

or is there a more clever way to do it in the statement directly?

Answer 1

You could use another number than 1 to add to the count. (Functionally, you can; you'd have to test performance.)

cqlsh> UPDATE mykeyspace.counts SET count = count + 1 where k1=1;
cqlsh> UPDATE mykeyspace.counts SET count = count + 1 where k1=2;
cqlsh> UPDATE mykeyspace.counts SET count = count + 1 where k1=3;
cqlsh> select * from mykeyspace.counts ;

 k1 | count
----+-------
  1 |     1
  2 |     1
  3 |     1

(3 rows)
cqlsh> UPDATE mykeyspace.counts SET count = count + 57 where k1=1;
cqlsh> select * from mykeyspace.counts ;

 k1 | count
----+-------
  1 |    58
  2 |     1
  3 |     1

(3 rows)
cqlsh> UPDATE mykeyspace.counts SET count = count + 65 where k1 in (2,3);
cqlsh> select * from mykeyspace.counts ;

 k1 | count
----+-------
  1 |    58
  2 |    66
  3 |    66