I’ve been working on a quiz section for one of my websites lately, well tried to anyway. One of the dumbest things happened. In a quiz you want to randomly pick questions and serve them to the visitor. That way they can answer them and shout WAHOO once they have one right.
Problem I had was that I couldn’t figure out how to sort all the questions in a completely random order, well semi random any way (since a computer does not know random as we do). I tried to do this by first counting the number of rows in the table and then creating a random array with 0..x (x being the number of rows). But this is very inefficient.
It requires two calls to the database for every visitor. One to get the number of rows and another to get the actual question. To most of you this might not seem like a problem, but try counting a few million rows. My second approach was already a bit better. I used the following statement:
SELECT * FROM QUESTION ORDER BY RAND();
It’s a lot better as it only requires one call to the database to retrieve all the questions for the visitor. The ‘ORDER BY RAND()’ part makes sure that the rows are sorted in a random order.
Note that I stated it is better, but far from perfect. Every time the same visitor hits a page this query will be executed, which defeats its purpose. So the next step is to cache all the query results for the visitors, which is a step I still have to make.