I recently got to work a site which was more like a directory. One of main trick there was to show random advertiser on the site so that each advertiser get’s some chance for displaying on sites front page. That was actually easy until this has to go with pagination. Back then I didn’t even knew if there’s any way to achieve mysql pagination with random ordering.
Problem is, you show some random advertisers on 1st page but on second page you must see another set of random advertisers (excluding what you already seen on 1st page) and so on. you see where’s it’s going?
Mysql Rand(seed) function to rescue
SELECT * FROM your_table ORDER BY RAND(351) LIMIT 10, 10;
“If a constant integer argument N is specified, it is used as the seed value. In the example above the result order is rand, but it is always the same. You simply change the seed and you get a new order.”
Here’s the summary of what we did:
- When user land’s on first page we generate a random number in php
- This random number serves as seed value to rand function of mysql in our query for page 1. and then this seed is saved to session.
- When user visits subsequent page i.e page 2,3 we use the seed value from session.
- when user lands on first page again, reset the seed and save new seed to session.
Trick here is, mysql’s rand() function will produce exactly same ordering until you change the seed value.
you should still stay aware that this might turn it to be a bit slower on larger datasets. In that case I would rather go with an extra column in my table and store a random number there and keep changing that periodically.
UPDATE table SET rand_col = RAND();
Share if you enjoyed reading it 🙂
Latest posts by Ankit Kumar (see all)
- All you need to know about Pivot Tables - July 21, 2016
- Case Study – How we managed to drastically improve API Performance - July 20, 2016
- Laravel Eloquent Tips and Tricks - November 30, 2015