I think you should try to rewrite condition to exclude OR like this: WHERE C >= 1 AND C <= 2. You can even do like this: WHERE C >= 1 AND C <= 3 AND (C = 1 OR C = 3). I think it will be faster than just ORed conditions alone.
Pavel On Thu, Jun 11, 2009 at 5:19 AM, Antti Nietosvaara<an...@ksenos.fi> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
