Optimized random select

[manaila]

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,r1.id
  FROM entries AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM people)) AS id
         ) AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 100;

But this one(not optimized) selects randomly:

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

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

[pritaeas]

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

[manaila]

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

[pritaeas]

Yes, so what is the output you want ?

[manaila]

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.

[pritaeas]

You mean this?

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

[manaila]

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?

[pritaeas]

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.