I compared the speeds and found them to be the same. RBS
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: 07 May 2008 22:25 To: General Discussion of SQLite Database Subject: Re: [sqlite] sorting records in random order Samuel Neff wrote: > This query runs slow: > > SELECT id FROM data ORDER BY random(); > > but this equivalent query runs very fast: > > SELECT id FROM (SELECT id, random() r FROM data) ORDER BY r; > I couldn't see how these would be different so I fired up the explain command. As I expected, these two produce identical code (except for the integer id assigned to the ephemeral table used for the sort). I don't think here will be any difference in speed between these two statements. SQLite version 3.5.7 Enter ".help" for instructions sqlite> create table t (id integer primary key, a text); sqlite> .explain sqlite> explain select id from t order by random(); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 explain select id from t order by random( ); 00 1 OpenEphemeral 1 3 0 keyinfo(1,BINARY) 00 2 Goto 0 26 0 00 3 OpenRead 0 2 0 00 4 SetNumColumns 0 0 0 00 5 Rewind 0 14 0 00 6 Rowid 0 1 0 00 7 MakeRecord 1 1 2 00 8 Function 0 0 3 random(-1) 00 9 Sequence 1 4 0 00 10 Move 2 5 0 00 11 MakeRecord 3 3 6 00 12 IdxInsert 1 6 0 00 13 Next 0 6 0 00 14 Close 0 0 0 00 15 OpenPseudo 2 0 0 00 16 SetNumColumns 2 1 0 00 17 Sort 1 24 0 00 18 Column 1 2 2 00 19 Integer 1 6 0 00 20 Insert 2 2 6 00 21 Column 2 0 1 00 22 ResultRow 1 1 0 00 23 Next 1 18 0 00 24 Close 2 0 0 00 25 Halt 0 0 0 00 26 Transaction 0 0 0 00 27 VerifyCookie 0 1 0 00 28 TableLock 0 2 0 t 00 29 Goto 0 3 0 00 sqlite> explain select id from (select id, random() r from t) order by r; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 explain select id from (select id, random () r from t) order by r; 00 1 OpenEphemeral 2 3 0 keyinfo(1,BINARY) 00 2 Goto 0 26 0 00 3 OpenRead 1 2 0 00 4 SetNumColumns 1 0 0 00 5 Rewind 1 14 0 00 6 Rowid 1 1 0 00 7 MakeRecord 1 1 2 00 8 Function 0 0 3 random(-1) 00 9 Sequence 2 4 0 00 10 Move 2 5 0 00 11 MakeRecord 3 3 6 00 12 IdxInsert 2 6 0 00 13 Next 1 6 0 00 14 Close 1 0 0 00 15 OpenPseudo 3 0 0 00 16 SetNumColumns 3 1 0 00 17 Sort 2 24 0 00 18 Column 2 2 2 00 19 Integer 1 6 0 00 20 Insert 3 2 6 00 21 Column 3 0 1 00 22 ResultRow 1 1 0 00 23 Next 2 18 0 00 24 Close 3 0 0 00 25 Halt 0 0 0 00 26 Transaction 0 0 0 00 27 VerifyCookie 0 1 0 00 28 TableLock 0 2 0 t 00 29 Goto 0 3 0 00 sqlite> Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
