0

I have a database with more than a million records. I'm trying to get the records in a randomized order. The database is filled with links to images and each image has an unique number. So I should be able to recreate the same "random" order for the next and previous buttons on the pictures?

Is there a way to come up with a query and save it on a cookie or session and reuse that to regenerate my random order?
Overall is it efficient because I don't want the load time to go high?

Any suggestion is awesome.

Improve this question
5
  • Do you need to restore "random" image after prev, next operations? Do you need random for each user? It's possible to have duplicates? Do you need real random or just unusual order? Commented Nov 15, 2013 at 8:16
  • You could also use shuffle() on the result array from your SQL query. php.net/manual/en/function.shuffle.php Commented Nov 15, 2013 at 8:21
  • @sectus, here are the answers to the questions: 1) I just want to go trough them, in the same order that was shown on the gallery 2) No It could be the same, but I want to change the order everything they click on "Shuffle Again" 3) No duplicates 4) unusual order is perfect Commented Nov 15, 2013 at 19:42
  • @DarkAshelin, Shuffle is perfect but how can you recreate same order in other pages? Commented Nov 15, 2013 at 19:43
  • @FarshadMomtaz save the final result array in a global variable. Commented Nov 15, 2013 at 22:35

2 Answers 2

Reset to default
4

The easiest way for this is, put in another column in your database, index it properly, and give it a random integer. Then you can ORDER your table on this, and have it be stable. This will make all users have the same, but random, sequence.

EDIT: If you want each user to have their own sequence, this too is simple enough, as Alma Do said:

SELECT * FROM records ORDER BY RAND(17)

will always have the same ordering (as long as you don't change the database), and different from

SELECT * FROM records ORDER BY RAND(2308)

But it will not be fast.

The only alternative I can think of would use a lot of space: when you calculate a sequence for a user, store it in a table. This would require a two-column table of million rows per user.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

That would work but every user will have the same order and the order can't be changed everytime the users click on "Shuffle"
That was not a part of your question, and we are not telepaths. Edited.
1

According to MySQL manual page, you can specify parameter for RAND() function and so it will return random, but repeatable sequence:

If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values.

That means, you'll be able to have random order, but still same order across your prev-next pages.

Improve this answer

1 Comment

Rand() works perfectly but when you have 1 Million records to go through it is really slow!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.