Selecting Random Rows in MySQL

Question

I am developing a quiz website, and I have a database which stores all of the questions. There are different types of quizzes, like math, science, history, etc. All of the questions are stored in one table.

My questions table looks like this:

questions ( qno(int)  ,type(int), question, .... ,... )

qno is the primary key, and type is used to keep track of the quiz type.:

if type = 1 (math)
 type = 2(science)

Now, I want to select some random questions for each type of test. For example, I may want to select some random 20 questions for only the math test.

Does MySQL have some way to select random rows?

Answer 1

You can use the rand function in MySQL to order the rows, and then take the top 10 (or however many you want) with limit.

select * from table order by rand() limit 10

If you want just the math questions:

select * from table where type = 1 order by rand() limit 10

Answer 2

Add a column to your table that will hold a UNIX timestamp.

Once a day or any timeframe which works for you, you run a query that updates that column.

In this case your query should run at midnight and look something like this.

UPDATE table SET rand_id = (UNIX_TIMESTAMP() + (RAND() * 86400));

Then to retrieve the rows, you use a query similar to this.

SELECT * FROM table WHERE rand_id > UNIX_TIMESTAMP() ORDER BY rand_id ASC LIMIT 20

Using a column with a pre-determined random value saves you needing to run a randomization function for every single row in the table for every single request to the page.

Answer 3

Another possibility is to generate a random permutation, store this in a session (or just the part you need, the algorithm is easily adaptable), and fetch the questions when you need them.

Answer 4

This is fairly straight forward. Assuming that your questions table is relatively small (a few thousand rows), you should SELECT FROM questions, order them by RAND( ) and LIMIT the result to 20. The query would look something like:

SELECT * FROM Questions ORDER BY RAND( ) LIMIT 0, 20

Also see if this helps: Selecting Random Records from a Table