[Simon Slavin] > On 15 Apr 2012, at 1:31pm, Steinar Midtskogen <stei...@latinitas.org> wrote: > >> Another question about max()/min() optimisation. Is there a way I can >> implement a virtual table so that max()/min() of a sorted >> (incrementing) column (which could be an integer primary key in a >> regular table) gets fast? > > The max() and min() functions work instantly if the columns they're > looking at are indexed. They simply find the first or last entry in > the index. So when defining your virtual table routines, just make > sure your key columns have an index, and that your xBestIndex method > finds the right index.
According to the "Using SQLite" book, "you cannot create an index on a view or on a virtual table". Also, when declaring the virtual table using sqlite3_declare_vtab the book says: "any constraints, default values, or key definitions within the table definition are also ignord - this includes any definition of INTEGER PRIMARY KEY as a ROWID alias". So, is there really a way to create an index in a virtual table, or a way to emulate this? My xRowid function simply returns the value of the "unix_time" column, but even "select max(rowid)" is equally slow. -- Steinar _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
