>
>I would first create an INTEGER primary key and then place an index on
>name,
>another on i_from, and another on i_to, and then see if the approach below
>has any benefit.
>
>When I tried this with a geo-queryit was actually slower than the standard
>select, and I'm curious if that's always going to be the case. It will
>come
>down to how efficient the INTERSECT of the vectors of integers is. Each
>vector will have been the result of an index-scan. If INTERSECT were
>optimized (perhaps with a minimal perfect hash function
>http://cmph.sourceforge.net/index.html) this approach might be useful.
All three following queries use only simple indexes (PK, name, lo, hi).
Query#1:
select * from tst where lo < 345678
intersect
select * from tst where hi > 123456
intersect
select * from tst where name = 'aaelj';
Query#2
select * from tst
join (
select rowid from tst where lo < 345678
intersect
select rowid from tst where hi > 123456
) as interval
on tst.rowid = interval.rowid and name = 'aaelj';
Query#3
select * from tst
join (
select rowid from tst where lo < 345678
intersect
select rowid from tst where hi > 123456
intersect
select rowid from tst where name = 'aaelj'
) as interval
on tst.rowid = interval.rowid;
On a 200K-row test table with random data, queries #2 and #3 were
essentially identical while #1 was twice slower (moving too much data
around, uselessly).
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
