>  You have three basic conditions, and they're all AND'ed together.
>  Just build an index that each condition can walk through.
>
>  Or am I missing something?  I know there are some odd rules about how
>  SQLite will use (or won't use) indexes for greater-than/less-than
>  conditions, but I don't remember the specifics.

This specifics is the same for any DBMS: if you have greater/less
condition on column that is "in the middle of index" then indexing on
any consecutive columns is useless. I.e. in this case with index on
(i_name, i_from, i_to) and condition i_from < something condition on
i_to will be checked for each row satisfying conditions on i_name and
i_from. So performance will be almost the same. "Almost" because when
i_to is in the index optimization can be made and row from table not
loaded unless condition on i_to is true. But I don't know if such
optimization exists in SQLite or not. Also this optimization can be
minimal in SQLite because AFAIK it stores part of the row from table
in the index too.


Pavel

On Thu, May 13, 2010 at 10:16 AM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> On Wed, May 12, 2010 at 06:00:29PM +0200, Jan Asselman scratched on the wall:
>> Hi,
>>
>> Given the following table with large row count 'row_count':
>>
>> CREATE TABLE table
>> (
>>       i_name  TEXT,
>>       i_from  INTEGER,
>>       i_to            INTEGER,
>>       i_data  BLOB
>> )
>>
>> I am wondering what would be the fastest way to get all rows with a
>> given name 'myname' that intersect with a given interval [a, b]?
>>
>> CREATE INDEX idx_from ON table (i_name, i_from);
>> CREATE INDEX idx_to ON table (i_name, i_to);
>
>  The query is only going to be able to use one of these.
>
>> I know this is exactly what a one dimensional R-tree index is used for,
>> but my project requires 64 bit integer minimum- and maximum-value
>> pairs...
>
>  True, although R-trees become much more useful when you get past one
>  dimension.
>
>> All suggestions or corrections are appreciated.
>
>  Assuming i_name is somewhat unique, why wouldn't you just create an
>  index over (i_name, i_from, i_to)?  If i_name isn't very unique, mix
>  up the  order a bit.
>
>  You have three basic conditions, and they're all AND'ed together.
>  Just build an index that each condition can walk through.
>
>  Or am I missing something?  I know there are some odd rules about how
>  SQLite will use (or won't use) indexes for greater-than/less-than
>  conditions, but I don't remember the specifics.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> _______________________________________________
> 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

Reply via email to