# UPDATE random rows

I'm working on creating a more efficient way to UPDATE random rows in a ticket allocation website we have.

Currently, we pre populate the tickets table with ticket numbers in subsequent order, i.e 1,2,4 etc. Each ticket number being a row.

When a customer then places their order we use the following SQL query to reserve their random tickets.

``````UPDATE tickets
SET order_item_id = X
WHERE lottery_id = X
AND order_item_id IS NULL
ORDER BY RAND()
LIMIT n
``````

To begin with the query above worked fine but as the table has grown we're now experiencing performance issues.

The concept I have in mind is to change the population of the tickets table to populate the tickets table in a random order and then use the primary ID key in the tickets table instead to order by like so:

``````UPDATE tickets
SET order_item_id = X
WHERE lottery_id = X
AND order_item_id IS NULL
ORDER BY id
LIMIT n
``````

My question is, how much more efficient is this method compared to the RAND() function or is there any better ways of doing what I'm trying to achieve?

UPDATE

Because the ticket numbers are already pre populated in the tickets table at random when a competition is launched, i'm thinking the below could be a better solution?

``````UPDATE tickets
SET order_item_id = X
WHERE lottery_id = '12102'
AND order_item_id IS NULL
AND id IN (SELECT id
FROM tickets
WHERE lottery_id = '12102'
AND order_item_id IS NULL
)
ORDER BY id
LIMIT 3
``````

Example of tickets table after tickets have been pre populated.

id ticket_number order_item_id
163895 1 NULL
163896 500 NULL
163897 1221 NULL
163898 20 NULL
163899 403 NULL
163900 8650 NULL
163901 2456 NULL

Ideally, it would be good it the first person who ordered 1 ticket got ticket number 1. And then if the next customer orders 2 tickets, they will get 500 and 1221

ANOTHER UPDATE

After some experimenting i found this query to work quite well but would welcome some thoughts!

``````UPDATE tickets
SET order_item_id = '120'
WHERE lottery_id = '12094'
AND order_item_id IS NULL
AND id >= (SELECT RAND())
LIMIT 3
``````
• "how much more efficient is this method" -- what did your tests show? Mar 4, 2021 at 13:46
• It actually performed slower than the RAND() function but i'm not sure if thats related to the dataset i'm testing it on Mar 4, 2021 at 13:50

Assuming you have an index on the `id` field, ordering by it should definitely be significantly faster than ordering by `RAND()`, the only thing is by randomly generating the `id` you'll likely end up with a lot of index fragmentation which could also affect performance depending on the size of your table.

Why not just populate your tickets table in a normal order, and generate the N number of random numbers you want in a separate statement first, then your `UPDATE` query becomes:

``````UPDATE tickets
SET order_item_id = X
WHERE lottery_id = X
AND order_item_id IS NULL
AND id IN (RandomNumber1, RandomNumber2, RandomNumber3)
``````

That would likely be one of the fastest ways to do it, by decoupling the random number generation first, and then using those numbers as part of your `WHERE` predicate (again assuming `id` is indexed) so index seeks can occur.

Not sure what database system you're using (you should tag it and the version) but in most modern RDBMS, you can even make the random number generation it's own subquery to your `WHERE` clause predicate. Here's an example with a user-defined function (again this will depend on your database system) that returns a table with N number of random numbers in it:

``````UPDATE tickets
SET order_item_id = X
WHERE lottery_id = X
AND order_item_id IS NULL
AND id IN (SELECT RandomNumber FROM fn_GetRandomNumbersTable(10)) -- Returns a table of 10 random numbers
``````

As far as generating the actual random numbers, again that'll depend on your specific database system for what capabilities it has to do so. One way in SQL (though there's no reason this couldn't be done in procedural code of the consuming application) is to use a loop and fill a temp table with the numbers. While SQL is best at relational logic, it can handle certain procedural tasks pretty well too such as just running the `RAND()` function N number of times (assuming N is a reasonable number, e.g. less than 1 billion). So an example of what that could look like is this (take this as pseudo-code since I don't know what system you're on, but this is T-SQL):

``````CREATE TABLE #RandomNumbers (RandomNumber INT)
DECLARE @LoopCounter = 0

WHILE (@LoopCounter < N) -- Fill in the number you want for N
BEGIN
INSERT INTO #RandomNumbers
SELECT RAND()

SET @LoopCounter = @LoopCounter + 1
END

UPDATE tickets
SET order_item_id = X
WHERE lottery_id = X
AND order_item_id IS NULL
AND id IN (SELECT RandomNumber FROM #RandomNumbers)
``````
• Thanks, this sounds promising. However, I'm not sure how I would generate the N numbers before inserting them into the UPDATE query. Do you think just having a separate SELECT statement would be sufficient? Mar 4, 2021 at 13:53
• @ShaunLippitt I'm not sure what database system you're on, so your capabilities of generating the random numbers will depend on that. I added an additional example of one way, assuming you have the ability to make table valued functions. But honestly, probably generating the series of random numbers would be best procedurally, probably in a loop, either at the database level or probably preferably the consuming application, and you either parameterize your `UPDATE` statement or generate it dynamically in procedural code.
– J.D.
Mar 4, 2021 at 13:59
• @ShaunLippitt Please see my latest update to my answer with pseudo-code on one way to generate then with a loop. Hopefully that helps give you ideas.
– J.D.
Mar 4, 2021 at 14:07
• Hi J.D, I've updated my question with an example i'm trying. I don't need to randomly generate the ticket numbers but more the row which is returned as the tickets are already pre populated when a competition is launched. Mar 4, 2021 at 14:08
• Thanks J.D. The id column is set to auto increment. Will the random function always deliver a random number to keep up with the incrementing? Mar 4, 2021 at 14:16