CODEWITHSUNDEEP
phpmysql
Newbie1
Newbie1

Reputation: 31

Best way to have random order of elements in the table

I have got table with 300 000 rows. There is specially dedicated field (called order_number) in this table to story the number, which is later used to present the data from this table ordered by order_number field. What is the best and easy way to assign the random number/hash for each of the records in order to select the records ordered by this numbers? The number of rows in the table is not stable and may grow to 1 000 000, so the rand method should take it into the account.

Upvotes: 2

Views: 225

Answers (4)

ludesign
ludesign

Reputation: 1373

I know you've got enough answers but I would tell you how we did it in our company.

The first approach we use is with additional column for storing random number generated for each record/row. We have INDEX on this column, allowing us to order records by it.

id, name   , ordering
1 , zlovic , 14
2 , silvas , 8
3 , jouzel , 59

SELECT * FROM table ORDER BY ordering ASC/DESC

POS: you have index and ordering is very fast CONS: you will depend on new records to keep the randomization of the records

Second approach we have used is what Karl Roos gave an his answer. We retrieve the number of records in our database and using the > (greater) and some math we retrieve rows randomized. We are working with binary ids thus we need to keep autoincrement column to avoid random writings in InnoDB, sometimes we perform two or more queries to retrieve all of the data, keeping it randomized enough. (If you need 30 random items from 1,000,000 records you can run 3 simple SELECTs each for 10 items with different offset)

Hope this helps you. :)

Upvotes: 0

Johan
Johan

Reputation: 76547

UPDATE table SET order_number = sha2(id)

or 

 UPDATE table SET order_number = RAND(id)

sha2() is more random than RAND().

Upvotes: 1

Karl Laurentius Roos
Karl Laurentius Roos

Reputation: 4399

If you don't want to use MySQL's built in RAND() function you could use something like this:

select max(id) from table;
$random_number = ...
select * from table where id > $random_number;

That should be a lot quicker.

Upvotes: 1

Cyclone
Cyclone

Reputation: 18295

Look at this tutorial on selecting random rows from a table.

Upvotes: 1

Related Questions