On 11 Jun 2009, at 10:19am, Antti Nietosvaara 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 apologise: of course you can't create an index on views. You're quite right. I was thinking you were reading from a temporary table, not a temporary view. So we now wonder if a view is really necessary. You can create a temporary table and read the records from the view into that. Or even ignore that stage entirely and include your restriction on 'C' in your SELECT command. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
