The following optimized select query select consecutive rows, i.e it chooses the first random row and the following ones will just be the ones below it:

SELECT name,
  FROM entries AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM people)) AS id
         ) AS r2
 LIMIT 100;

But this one(not optimized) selects randomly:

SELECT distinct name, id FROM people

How do we make the first random select query to function like the second?

Recommended Answers

Can you explain what you mean, perhaps with an example?

Jump to Post

Yes, so what is the output you want ?

Jump to Post

All 7 Replies

I have a table named people with more than 500 records.
When I randomly select, say, 6 people, using optimized random select, I get results of this kind:

name    id
bob     300
tom     301
roy     302
wety    303
abi     304
rex     305

But when selecting using the unoptimized method I get:

name    id
ramon   505
rhykie  23
monet   101
sakil   143
modasy  86
felix   497

I am okay with the second output.
But the first one, it selects consecutive rows, starting from the random row. I want it to select all the rows randomly like the second method.

You mean this?

    SELECT distinct name, id FROM people ORDER BY RAND() LIMIT 100
) T

Yeah, it also works fine but it still takes more time to return results like the second method which is not optimized.
The first optimized method took less time, its just that it returned consecutive rows.
But well, according to your skills and experience do think your method is more optimized than my normal(second method) unoptimized method?

It's a sub-select, so it's always slower (and less optimized) than the query without it, especially when using RAND(). It all depends on what you need to use this for. In some cases other methods may be far better.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.