On Thursday 11 June 2009 11:50:56 Simon Slavin wrote: > On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote: > > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2); > > where (C=1 OR C=2) will reflect the permissions of the user. > > > > There is also an index for C: > > CREATE INDEX idx_C ON T(C); > > > > I have a problem with performance when using the view for simple > > selects. > > The following query returns the result immediately: > > SELECT min(C) from T; > > > > However the same query on the view takes a very long time: > > SELECT min(C) from T_view; > > You didn't create an index on T_view ?
No, it seems you cannot create an index on views. Trying to do so resulted: "SQL error: views may not be indexed" I just noticed that if there I use only one C filter, for example: CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE C=1; the queries will be just as fast as with using T directly. Also, explain query plan returns the string ending with ORDER BY, just like it does when selecting from T. With this single filter my query took about 8 ms. With two filters (C=1 OR C=2) the time went to over 6 seconds. With (C=1 OR C=2 OR C=3) about 13 seconds. -- Antti Nietosvaara _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
