Antti Nietosvaara wrote:
> 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);
You may find that an index on a column with only a few distinct values
actually hurts performance. Something as simple as
select * from T where C=1;
may take longer with an index. You see, it takes O(N) time to run this
query without an index, and O(M (logN)^2) time to run it with index,
where N is the total number of records in the table, and M is the number
of records satisfying the condition. So the index has a clear benefit
when M is much smaller than N, but is obviously detrimental when M is
close to N. The break-even point occurs approximately at M equal to 10%
of N.
> 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;
Because this query is no longer a simple select. It is translated
internally into
select min(C) from
(SELECT * FROM T WHERE (C=1 OR C=2));
Index is used to satisfy the WHERE condition - but that's still a
substantial portion of all records in T, which then have to be scanned
linearly.
> Is there a way to speed up the queries
Well, if you are talking of this particular query, you can do something
like this:
select (case when exists (select 1 from T where C=1) then 1
when exists (select 1 from T where C=2) then 2
else null end);
This should run fast. However, I kind of doubt that "SELECT min(C) from
T_view;" is a typical query in your application.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
