A random string from a large query result

Question

I need to get 1-2 rows from the result of a query obtained using SQL select on indexed columns, without getting the whole set of records.

For example, I will get 10,000 records using the query

SELECT * FROM table WHERE field 1>1 AND field1 < 10

but I need only 1 random row from this query regarding a large load of my database.

I can use

SELECT * FROM table WHERE field 1>1 AND field1 < 10 LIMIT 100, 1

But I don't know that numebr entries use the correct offset range

How can I achieve this?

Answer 1

You can use ORDER BY RAND ()

SELECT * FROM table WHERE field1 > 1 AND field1 < 10 ORDER BY RAND() LIMIT 1

This returns 1 random string with field 1 between 1 and 10

Answer 2

What about limiting the entries that you select first?

SELECT * FROM table WHERE field1 IN (CONVERT(RAND()*10,SIGNED),CONVERT(RAND()*10,SIGNED)) LIMIT 2