CODEWITHSUNDEEP
phpmysqlsqlrandom
Farshad Momtaz
Farshad Momtaz

Reputation: 424

Regenerate "random" MySQL query

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.

Upvotes: 0

Views: 128

Answers (2)

Amadan
Amadan

Reputation: 198408

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.

Upvotes: 4

Alma Do
Alma Do

Reputation: 37365

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.

Upvotes: 1

Related Questions