0

Is it better to select random data with pure SQL, or just select entire table and shuffle it? If pure SQL is better could someone show me an example of the most efficient way of doing it?

Also is it a good idea to save this selected data into a session variable? I want to make quiz with one question per page and with ability to get back to answered questions and change its answer. The session would save a double array of questions and answers to them that user picked. Or maybe there is a more efficient way of doing this?

3 Answers3

0

Definitely, it's better to select random data with pure SQL. Here you can read how to select random rows in mysql.

You can save this data on the server side (SESSION) or on the client side (COOKIE). But it's not principal in this case. Most importantly, that the application should have access to user questions and answers after the last question

Community
  • 1
  • 1
alexander.polomodov
  • 5,396
  • 14
  • 39
  • 46
  • Is it efficient to save questions and answers in cookies or sessions? Maybe there is a more efficient way? –  Mar 06 '16 at 22:12
  • What's the efficiency in our case? Space, proccessing speed, safety of data? Once I created module with some contest for users (for popular russian news site). There are was quizzes with multiple-choice questions and questions with detailed answers. Moderators chose winners for most right answers + creative answers for open questions. These contests were with some real prizes, so I need to store users data into database and create admin panel for it. But there are was also simple tests like yours and their store data into cookies. And it still works fine even I left that place a while ago:) – alexander.polomodov Mar 06 '16 at 22:24
  • Mostly processing speed on a low spec server. Thanks for your answer. –  Mar 08 '16 at 11:45
0

The "Order by rand()" function in MySQL is really slow and should usually not be used. Either shuffle after selection or, preferably, randomize a row reference and then do your query selection. The latter alternative is the most efficient one if it can be used.

purpleninja
  • 376
  • 3
  • 11
0
SELECT bar, foo, rand() FROM tabname ORDER BY 3 LIMIT 0,1;

Of course you can use AS and so own and the LIMIT is just important if you wanna output some single data ( like questions for some game ).

P.S - Some people will say that rand() is to slowly, but that's not true, it just depends how you and where you will use it. In this simple way above, it is pretty fast and if you making a quiz which has under 150 000 questions :-), it is just ok.

Hope i could help you. Cheers.

Denis Komnenovic
  • 44
  • 4