Testing for my e mail address -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Steinar Midtskogen Sent: 15 April 2012 18:38 To: General Discussion of SQLite Database Subject: Re: [sqlite] Why are two select statements 2000 times faster thanone?
[Kit] > 2012/4/15 Steinar Midtskogen <stei...@latinitas.org>: >> So, is there really a way to create an index in a virtual table, or a >> way to emulate this? > > Why? You don't need this. Use index on base tables. My base tables are indexed. Let's say I want to make a very simple virtual table this way: create virtual table vtab using copy(indexed_table); which simply maps any query for vtab to indexed_table and returns that. So let's say that indexed_table have an integer column "key" which also a primary key. So "select max(key) from indexed_table" will be fast no matter how big it is and the module can find this value in a blink. What I would like to is to have "select max(key) from vtab" run fast as well, without having to run through the billion rows in index. So what happens when I run "select max(key) from vtab"? Well, all xFilter will know is that it needs to produce the "key" column, and there should be a "order by key" clause as well, but even if we can assume that what we're dealing with is a sorted column, and xFilter could look up the max in no time, xFilter doesn't know that the query is for the max value. Can my module do anything better than to produce all the rows for sqlite to feed into the max aggregate function? >> My xRowid function simply returns the value of the "unix_time" >> column, but even "select max(rowid)" is equally slow. >> Steinar > > Why you need "select max(rowid)"? Something is wrong in your data > design. Use autoincrement. I don't need it, but a virtual table must provide one. I'm not sure why. -- Steinar _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ----- No virus found in this message. Checked by AVG - www.avg.com Version: 2012.0.1901 / Virus Database: 2411/4938 - Release Date: 04/15/12 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users