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?