You mean, of course, doing a re-write as:
select *
from T
where rowid in (select rowid
from T
where a < 1
UNION
select rowid
from T
where b < 1)
which uses four indices. one on a, one on b, one on rowid, and one for the
union. Depending on the number of columns and rows, you could also do:
select *
from T
where a < 1
UNION
select *
from T
where b < 1
Nevertheless, each traversal operation is only using one index at a time.
If you have an index containing on T(a,b) and/or and index on T(b,a) you also
add a couple more possible ways to execute the query. Which one has the
highest performance depends on the data in the table and the available indices.
Choosing the optimal requires application knowledge beyond what would be
available to mere statistical optimizers.
>-----Original Message-----
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of James K. Lowden
>Sent: Sunday, 2 February, 2014 09:16
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Fine tuning table indexes
>
>On Sat, 1 Feb 2014 11:21:45 -0500
>Stephen Chrzanowski <pontia...@gmail.com> wrote:
>
>> Would be interesting to see when and where that single index comes
>> into play when multiple indexes are defined.
>
>create table T (t int primary key, a int , b int);
>create index Ta on T(a);
>create index Tb on T(b);
>
>select * from T where a < 1 or b < 1
>
>Using one index, pick your poison. You could use Ta or Tb, but for the
>other half of the OR you'll have to scan the table. So you might as
>well scan the table and forget about the index.
>
>Using two indexes, you can find the matching values in Ta and Tb, and
>produce results from the union of the two.
>
>For 10 rows, the two-index algorithm is likely slower. For a million
>rows, depending on the cardinality of the data, it could be
>significantly faster.
>
>--jkl
>_______________________________________________
>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
