More efficient strategy than randomly selecting rows that meet condition in MySQL?

Max J. 0
Viewed 80

Imagine I would like to develop something similar to Tinder. I have a database with roughly 170k rows (=persons) and I would like to present them on my website. After the user's response, the next person is shown etc.

Once a person has been shown, this is marked in the column 'seen' with a 1. The order in which the persons are shown should be random and only persons that have not been seen yet should be shown.

At the moment, I have this solution. However, this is rather slow and takes too much time for a smooth experience. What would be a more efficient approach to this problem? What is the gold standard for such problems?

SELECT * FROM data WHERE (seen = 0) ORDER BY RAND() LIMIT 1
mysql database sql algorithm
1 Answers

Add a non-clustered index on the 'seen' column and PK column which will improve quering on the same.

If the primary id is sequential and u know the limits of the records, you can get a random number between max value and min value and query like

SELECT * FROM data WHERE seen = 0 and id >= random_id LIMIT 1
WisdomSeeker0
Related