Categories
Database Development

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?

Leave a Reply

Your email address will not be published. Required fields are marked *