Return random rows from mysql database without using rand ()

Question

I would like to be able to pull 15 or so records from the database. I have seen that using WHERE id = rand()can cause performance issues as my database gets bigger. All the solutions that I saw are aimed at choosing one random record. I would like to get multiplicity.

Does anyone know of an efficient way to do this for large databases?

edit:

Further editing and testing:

I made a fairly simple table in a new database using MyISAM. I gave these 3 fields: autokey(unsigned auto number key) bigdata(large blob) and somemore(medium int). Then I applied random data to the table and completed a series of queries using Navicat. Here are the results:

Query 1: select * from test order by rand() limit 15

 Query 2: select * 
          from 
      test 
          join 
      (select round(rand()*(select max(autokey) from test)) as val from test limit 15)                                           as rnd
      on 
          rnd.val=test.autokey;`

(I tried both select and select the selection, and this showed no differences)

and

Query 3 (I only ran this on the second test):
SELECT  *
    FROM    (
    SELECT  @cnt := COUNT(*) + 1,
            @lim := 10
    FROM    test
    ) vars
    STRAIGHT_JOIN
    (
    SELECT  r.*,
            @lim := @lim - 1
    FROM    test r
    WHERE   (@cnt := @cnt - 1)
            AND RAND(20090301) < @lim / @cnt
    ) i

ROWS: QUERY 1: QUERY 2: QUERY 3:
2,060,922 2.977s 0.002s N / A

3,043,406 5.334s 0.001s 1.260     

I would like to make more lines so that I can see how query 3 scales, but at the moment it seems that query 2 is the clear winner .

, , - - ?

Answer 1

:

select * from table order by rand() limit 15

(, , ) . , . postgres ( MySQL )

select * from table join 
   (select (random()*maxid)::integer as val from generate_series(1,15)) as rnd
   on rand.val=table.id;

maxid id table. id , 15 , .

UPDATE

, MySQL , generate_series. . :

select * 
from 
 table 
join 
 -- this just returns 15 random numbers. 
 -- I need `table` here only to produce rows for rand()
 (select round(rand()*(select max(id) from table)) as val from table limit 15) as rnd
on 
 rnd.val=table.id;

P.S. , , ( [...]) .

Answer 2

. . mySQL .

id = rand() -, PHP, , , . LIMIT, .

PHP.

,

• rand(), 0

• ,

• ,

• , 1

• ,

, . , LIMIT rand() .

LIMIT, @Luther, , .

Answer 3

, , , mysqli_fetch_all, :

shuffle($a);
$a = array_slice($a, 0, 15);

Answer 4

,

select * from table order by rand() limit 15

.

,

select * from table where no >= rand() limit 15

select * from table where no >= $rand and no <= $rand+15

, , ​​ , .

Answer 5

, MySQL , -

select * from table where id in (select id from table order by rand() limit 15)