1

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?

tshepang
  • 12,111
  • 21
  • 91
  • 136
Pavel
  • 19
  • 3

2 Answers2

0

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

JochenJung
  • 7,183
  • 12
  • 64
  • 113
  • On database with 10kk records you will down your server and loose all users ;-) – Pavel Aug 17 '10 at 12:18
  • Thats true. Then you will need to use two queries like described in this article: http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ – JochenJung Aug 17 '10 at 12:32
  • count(*) not a variant on big mysql query results, it will take some seconds to calculate calc(*) with WHERE on 1kk row table. I have some ideas, but I'm not sure in their correctness yet. – Pavel Aug 17 '10 at 13:22
0

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
KMW
  • 109
  • 7
  • I have criteria on field1 how can I choose it rand? – Pavel Aug 17 '10 at 13:18
  • if the criteria is a range, you can create a random number that would fall in that range – KMW Aug 17 '10 at 14:30
  • but there are many rows within this selection even when I've choosen criteria and I should take not first but random row. Anyway thank you've pushed me on interesting idea also. – Pavel Aug 17 '10 at 14:35