Just an idea: If the table has an INTEGER PRIMARY KEY AUTOINCREMENT then then maximum rowid is available in the sqlite_sequence table. To generate a random sequence of rowids, this can be used, somewhat like
SELECT DISTINCT rnd() * (SELECT seq FROM sqlite_sequence WHERE name='<table_name>') as rowid; Maybe as a view or a CTE. Then use this as the LHS of a join SELECT t.* from (...) random cross join <table_name> t on random.rowid = t.rowid LIMIT x; Creating random rowids should be blindingly fast, even with storing the already created rowids in a temp btree. Reading the table via rowid is as fast as possible. Any "misses" in the rowid generation (i.e. rowids not present) are compensated by the number of rowids generated. Speed increases with the desity of rowids (i.e. <current_row_count>/<max_inserted_rowid>). Unless of course SQLite chooses to create all the rowids before providing the first one. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Stephen Chrzanowski Gesendet: Freitag, 01. Juni 2018 18:47 An: SQLite mailing list <[email protected]> Betreff: [EXTERNAL] Re: [sqlite] random rows 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 [RandomTable]([PriID], [DataCol]) VALUES(3, 'c'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(4, 'd'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(5, 'e'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(6, 'f'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(7, 'g'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(8, 'h'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(9, 'i'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(10, 'j'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(11, 'k'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(12, 'l'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(13, 'm'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(14, 'n'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(15, 'o'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(16, 'p'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(17, 'q'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(18, 'r'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(19, 's'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(20, 't'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(21, 'u'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(22, 'v'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(23, 'w'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(24, 'x'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(25, 'y'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(26, 'z'); select random() as R,* from RandomTable order by R limit 10; Using 3.23.1 On Fri, Jun 1, 2018 at 10:22 AM, Don V Nielsen <[email protected]> wrote: > ?? > > 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 <[email protected]> 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 > > [email protected] > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
