Query first, then randomize, and then retrieve remaining fields

Question

I have a table with about 70 columns and 120,000 rows of data. What I want to do is randomize a record and then displaying the values of others columns of this record. If I do fetch all data,

$result=mysqli_query($link, 'SELECT id, column1, column2, column3, ..., column 70 from table');
while ($row=mysqli_fetch_array($result))
{
  $info[] = array('id'=>$row['id'], 'column1'=>$row['column1'], ...); 
}
$randindex = rand(0,count($info));
$id = $info[$randindex]['id'];
echo $info[$randindex]['column1']; echo $info[$randindex]['column2']; ....

I'm afraid that this will significantly slow down the process. So I want to query only the ID before randomization, and then use the randomized ID to retrieve the other values of that record in the database.

$result=mysqli_query($link, 'SELECT id from table');
while ($row=mysqli_fetch_array($result))
{
  $info[] = $row['id']; 
}
$randindex = rand(0,count($info));
$id = $info[$randindex];

and then retrieve all other fields of this particular record somehow. I asked how to do this in SQL here but I would like to know if there is any more efficient way by other means besides SQL. Do I need to do a loop like this?

You want to do something yourself better than the database.. hmm. Is there a particular reason for you not to want all the columns at once, but instead run 2 queries? — FirstOne, Jul 16 '16 at 16:26

score 1 · Answer 1 · answered Jul 16 '16 at 16:25

In your code, do the following:

select min(id) as minid, max(id) as maxid
from table;

Then use php to generate a random id and do:

select t.*
from table t
where t.id >= $randid
order by id
limit 1;

With an index on id -- and reasonable assumptions about there not being too large gaps in the values -- then this will work well.

You can do the same thing in just one query:

select t.*
from table t cross join
     (select min(id) as minid, max(id) as maxid from table) tt
where t.id >= minid + rand() * (tt.maxid - tt.minid)
order by id
limit 1;

Marko Krstic · Accepted Answer · 2016-07-16T16:33:47.037

0

you can use ORDER BY RAND() directly in the sql query:

SELECT * FROM table ORDER BY RAND() LIMIT 1

ORDER BY RAND() actually makes random order of you rows, and then you just do LIMIT 1 in order to get only one row, the first one.

edited Jul 16 '16 at 16:33

answered Jul 16 '16 at 16:28

Marko Krstic

1,417
1
11
13

score 0 · Answer 3 · answered Jul 16 '16 at 16:36

0

I do not think it is valid. I think it's faster execute just one query with ORDER BY RAND ()

answered Jul 16 '16 at 16:36

Lucas Martins

508
6
9

Query first, then randomize, and then retrieve remaining fields

3 Answers3