Hello! create view view_role as select role.id as rowid, role.uid, role.timestamp, role.user_id, role_fts.* from role, role_fts where role_fts.rowid=role.id group by uid having timestamp=max(timestamp);
sqlite> explain query plan select * from view_role where rowid=1000; 0|1|TABLE role_fts VIRTUAL TABLE INDEX 0: 1|0|TABLE role USING PRIMARY KEY sqlite> select * from view_role where rowid=1000; 1000|�ƈ]�4b�cg�|2455285.05859278|veter|Role 1000|role 1000 CPU Time: user 17.385087 sys 0.556035 sqlite> select count(*) from view_role; 1000000 CPU Time: user 18.933183 sys 0.744047 sqlite> explain select * from view_role where rowid=1000; 0|Trace|0|0|0||00| 1|OpenEphemeral|3|7|0|keyinfo(1,BINARY)|00| 2|Integer|0|9|0||00| 3|Integer|0|8|0||00| 4|Gosub|11|64|0||00| 5|Goto|0|73|0||00| 6|VOpen|2|0|0|vtab:90C3C30:B6B08200|00| 7|OpenRead|1|2|0|4|00| 8|Integer|0|14|0||00| 9|Integer|0|15|0||00| 10|VFilter|2|25|14||00| 11|Rowid|2|17|0||00| 12|MustBeInt|17|24|0||00| 13|NotExists|1|24|17||00| 14|Column|1|1|18||00| 15|Sequence|3|19|0||00| 16|Rowid|1|20|0||00| 17|Column|1|2|21||00| 18|RealAffinity|21|0|0||00| 19|Column|1|3|22||00| 20|VColumn|2|0|23||00| 21|VColumn|2|1|24||00| 22|MakeRecord|18|7|17||00| 23|IdxInsert|3|17|0||00| 24|VNext|2|11|0||00| 25|Close|2|0|0||00| 26|Close|1|0|0||00| 27|Sort|3|72|0||00| 28|Column|3|0|13||00| 29|Compare|12|13|1|keyinfo(1,BINARY)|00| 30|Jump|31|35|31||00| 31|Move|13|12|1||00| 32|Gosub|10|50|0||00| 33|IfPos|9|72|0||00| 34|Gosub|11|64|0||00| 35|Column|3|3|18||00| 36|CollSeq|0|0|0|collseq(BINARY)|00| 37|AggStep|0|18|7|max(1)|01| 38|Column|3|2|1||00| 39|Column|3|0|2||00| 40|Column|3|3|3||00| 41|Column|3|4|4||00| 42|Column|3|5|5||00| 43|Column|3|6|6||00| 44|Integer|1|8|0||00| 45|Next|3|28|0||00| 46|Gosub|10|50|0||00| 47|Goto|0|72|0||00| 48|Integer|1|9|0||00| 49|Return|10|0|0||00| 50|IfPos|8|52|0||00| 51|Return|10|0|0||00| 52|AggFinal|7|1|0|max(1)|00| 53|Integer|1000|17|0||00| 54|Ne|17|51|1||6c| 55|Ne|7|51|3|collseq(BINARY)|6d| 56|SCopy|1|25|0||00| 57|SCopy|2|26|0||00| 58|SCopy|3|27|0||00| 59|SCopy|4|28|0||00| 60|SCopy|5|29|0||00| 61|SCopy|6|30|0||00| 62|ResultRow|25|6|0||00| 63|Return|10|0|0||00| 64|Null|0|1|0||00| 65|Null|0|2|0||00| 66|Null|0|3|0||00| 67|Null|0|4|0||00| 68|Null|0|5|0||00| 69|Null|0|6|0||00| 70|Null|0|7|0||00| 71|Return|11|0|0||00| 72|Halt|0|0|0||00| 73|Transaction|0|0|0||00| 74|VerifyCookie|0|14|0||00| 75|TableLock|0|2|0|role|00| 76|Goto|0|6|0||00| Best regards, Alexey Pechnikov. http://pechnikov.tel/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
