GROUP BY and RAND() - MySQL 4.1

[nunomira]

Hi,

I have a quiz with several questions and each question fits into a specific theme.

The table is something like:

id, theme, question

1 1 question1
2 2 question2
3 1 question3
4 2 question4
5 3 question5
6 3 question5
7 1 question6

Lets suppose I have 3 different themes.
I want to select 3 questions, randomly, each from a different theme.

With this:

SELECT id, theme, question
FROM `questions`
GROUP BY theme
ORDER BY RAND( )
LIMIT 3 

I always get the same three questions, just ordered randomly.
It selects the questions, each from a different theme, always with the same order, and only then, the rand() is used.

I tried using a subquery and it works.

SELECT id, theme, question
FROM
(
SELECT * FROM `questions`
ORDER BY RAND()
)
AS tbl
GROUP BY theme
LIMIT 3

I radomize the whole table first, and then get the 3 questions, each with a different theme.
The problem is that it doesn't look very efficient. I don't have many question, and it works. But what if I had thousands? Randomizing the whole table doesn't seem a very good idea.

Is there another way to accomplish this (just with MySQL - I know I could do it with PHP)?

Here's the DB in case you want to play with it:

CREATE TABLE `questions` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `theme` tinyint(4) NOT NULL default '0',
  `question` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

INSERT INTO `questions` VALUES (1, 1, 'question1');
INSERT INTO `questions` VALUES (2, 2, 'question2');
INSERT INTO `questions` VALUES (3, 1, 'question3');
INSERT INTO `questions` VALUES (4, 2, 'question4');
INSERT INTO `questions` VALUES (5, 3, 'question5');
INSERT INTO `questions` VALUES (6, 3, 'question6');
INSERT INTO `questions` VALUES (7, 1, 'question7');
INSERT INTO `questions` VALUES (8, 2, 'question8');