Most effecient method of using mysql's RAND() ?

[unsider]

I need to generate random results, maybe 5-10 random entries from a single DB table, (although this table could become quite large) and outputting them as quickly as possible. So therefore I need to optimize my query.

 

Also, I would like to prevent repetition.

 

Well we all know that I can use:

 

SELECT * FROM table ORDER BY RAND() LIMIT 0,9;

 

But from what I've read, it creates a temp file in the process of doing this (could be old info, didn't confirm), so this technically isn't the quickest way to do this.

 

Well I kept reading, and came up with this:

 

SELECT * FROM table WHERE id = (SELECT FLOOR( MAX(id) * RAND()) FROM table ) ORDER BY id LIMIT 1;

 

So my question is, is this a good method, are there better methods, what do YOU do to overcome this problem?

 

Thanks.

 

 

 

[fenway]

The latter method you describe is the most efficient... but it gets complicated if you need to join this, or if you want more than 1 record back.