[MySQL][Highload] Random row from big query result

Question

Hi all,

I need to get 1-2 rows from query result retrived with SQL select on indexed columns without getting the whole record set.

For example I will retrieve 10 000 records using query

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

but I need only 1 random row from this query regarding to highload of my database.

I can use

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

But I don't know records numebr to use correct offset range

How can I achieve this goal?

Thanks

Answer 1

You could use ORDER BY RAND()

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

This will return 1 random row with field1 in between 1 and 10

Answer 2

How about restricting the records you select in the first place?

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