Hi all, I have a table T with a few million rows. It has a column C with only a handful of distinct values for grouping the data. When a user wants to access the data my application reads it from a temporary view: 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; It would seem that the index is used but there are some differences: EXPLAIN QUERY PLAN SELECT min(C) from T; returns 0|0|TABLE T WITH INDEX idx_C ORDER BY but query EXPLAIN QUERY PLAN SELECT min(c) from T_view; returns 0|0|TABLE T WITH INDEX idx_C Is there a way to speed up the queries or maybe an alternative method of filtering results based on C? -- Antti Nietosvaara _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
