Slow Concurrent Updates

[Felix Baker]

Hi Guys,

We have a requirement for a high concurrency table 100+ requests per second.

It is a table that contains a bunch of unique key-codes, these are assigned a request_guid as they are requested.

The table contains an integer primary key, a unique keycode and a null request_guid.

We are running the below query, it works ok with 10,000 records however with 1million+ records it grinds to a halt and each update takes 16+ seconds.

Table:
(id INT
keycode VARCHAR(50)
request_guid VARCHAR(45) NULL)

Concurrant Query:
UPDATE coupon
SET request_guid = ?
WHERE request_guid IS NULL
ORDER BY RAND() LIMIT 1;

The RAND() order is being used otherwise we get locking issues when the same row is trying to be updated simultaneously.

Can anyone think of a better way of doing this to improve performance? Potentially the keys should be in a separate table?

I hope the above makes sense, thanks for your help!

Cheers,

Felix