Thread: limitby without orderby

Hi,

On 22 September 2011 21:32, Rohan Malhotra <yourbuddyrohan@gmail.com> wrote:
> Hi Gurus,
> What is difference between
> select * from items order by random() limit 5;
> and
> select * items limit 5;
> my basic requirement is to get random rows from a table, my where clause

This one says: give me first five rows which you have around. Usually
rows are from cache and you can get the same result after each
execution. On the other hand the first query returns different result
set every time.

If you know approximate number of rows in "items" table then you can
use this (and avoid sort):
select * from items where random() < 5.0 / total_rows limit 5

You can replace total_rows by this query: select reltuples from
pg_class where relname = 'items' (ie select * from items where random
> 5.0 / (select reltuples from pg_class where relname = 'items') limit
5)

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)

Rohan Malhotra wrote:
> What is difference between
>

> select * from items order by random() limit 5;
>

> and
>

> select * items limit 5;
>

> my basic requirement is to get random rows from a table, my where clause will make sure I won't get
> same rows in repeated execution of above queries.

The second query will not return the rows in a randomized order, but rather
in the order they are found. You should use the first query.

Yours,
Laurenz Albe

On Thu, Sep 22, 2011 at 5:32 AM, Rohan Malhotra
<yourbuddyrohan@gmail.com> wrote:
> Hi Gurus,
> What is difference between
> select * from items order by random() limit 5;
> and
> select * items limit 5;
> my basic requirement is to get random rows from a table, my where clause
> will make sure I won't get same rows in repeated execution of above queries.

The biggest problem with the order by random() limit 5 is the cost.
If there's 1,000 or so rows, no big deal, if there's 10,000,000 rows
it's gonna be slow...