I'm developing web service for my Android application. I have a lot of data in MySQL table. My service fetching this data randomly using RAND() function. I get a lot of records. So, I need to add load more functionality. But my data is constantly changing.
I've tried adding a limit but same data can come.
SELECT * FROM news ORDER BY RAND();
I want to come the data in sectional.
Can you give me an idea or method? Thanks.
One way to handle this would be by excluding the currently loaded items from the second query, for example:
SELECT * FROM news ORDER BY RAND() LIMIT 10;
Then, when making the second query you can exclude the items you've already loaded:
SELECT * FROM news WHERE `id` NOT IN(<loaded ids>) ORDER BY RAND() LIMIT 10;
This will ensure that there are no duplicates loaded.
MySQL is built to serve ordered data optimized by index and ORDER BY RAND() blows both of these to bits. If you don't want to go completely into the complexity of the link I sent, a simpler solution would be something like:
SELECT id FROM news;$_SESSION['news_shuffle'] = array_shuffle($results_from_previous); shuffle happens once per user, and PHP is better at randomizing things than MySQL.$_SESSION['news_shuffle_offset'] = 0; and increment it each time you pick an article.$stmt = $dbh->prepare('SELECT * FROM news WHERE id = ?');$stmt->execute([$_SESSION['news_shuffle'][$_SESSION['news_shuffle_offset']]);It's not the most elegant solution, but it also won't tip over your server once you have more than a handful of users/articles.