You may find it faster to do:
select c from t where rowid in (list-of-constants)
and generate the list of constants using something like a blackrock
permutation generator. That takes linear time, whereas all the order-by
variants are n.log(n). You need some sort of row-id generator function,
which you can do with windows/olap. Beware in Oracle, some of the rowid
variant functions only increment if you return the row.
sqlite seems to have a trick where copying the table creates sequential
rowids, by spec, if you don't have one.
S.
On 05/31/2018 08:28 PM, Jay Kreibich wrote:
I’m not entirely sure your solution will have an even distribution. It depends
a lot on how many times random() is called (once per row vs once per sort
operation), and how the sort algorithm works. I might do this instead:
SELECT * FROM (SELECT random(), t.* FROM t) ORDER BY 1 LIMIT 200;
As this makes sure random() is only called once per row. I’m pretty sure this
is actually equivalent to yours, but it makes things a bit more explicit.
If you only needed one, I’d do something like:
SELECT * FROM table ORDER BY rowid LIMIT 1 OFFSET abs(random()) % (SELECT
count(*) FROM table);
Or even just call that multiple times if you need a moderate number. The ORDER
BY should be free, but it could also be eliminated.
-j
On May 31, 2018, at 7:12 PM, Torsten Curdt <tcu...@vafer.org> wrote:
I need to get some random rows from a large(ish) table.
The following seems to be the most straight forward - but not the fastest.
SELECT * FROM table ORDER BY random() limit 200
Is there a faster/better approach?
cheers,
Torsten
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
