On 06/01/18 04:41 , Abroży Nieprzełoży wrote:
> 2018-06-01 2:12 GMT+02:00, Torsten Curdt :
>> 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
Here's my two cents. Don't spend it all in one place...
CREATE TABLE [RandomTable](
[PriID] INTEGER PRIMARY KEY AUTOINCREMENT,
[DataCol] CHAR);
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(1, 'a');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(2, 'b');
INSERT INTO
??
SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x)
Maybe. It is more memory efficient then trying to sort the entire lot of data.
On Thu, May 31, 2018 at 7:13 PM Torsten Curdt wrote:
> I need to get some random rows from a large(ish) table.
>
> The following
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,
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;
hmm... i think:
begin transaction;
query 'select count(*) from tab' to get total number of records, name it N
randomly select 200 integers from the range 0 to N-1
for each number query 'select * from tab limit 1 offset ?' with '?'
bound to selected number
end transaction;
2018-06-01 2:12
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
7 matches
Mail list logo
