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?
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.
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
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