On 3/22/2010 2:15 AM, Max Vlasov wrote:
>> Assuming a table where Latitude column and Longitude column each have
>> their own index:
>>
>> perform select #1 which returns the rowids of rows whose latitude meets
>> criteria
>> INTERSECT
>> perform select #2 which returns the rowids of rows whose longitude meets
>> criteria
>>
>>
>>
> Ok, just test.
>
> Created a base with a table
>
> CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT,
> [X] INTEGER,[Y] INTEGER)
>
> Filled with 1,000,000 records:
>
> INSERT INTO TestTable
> (X, Y)
> VALUES
> ((random() % 50000) + 40000, (random() % 50000) + 40000)
>
> Final size: 44M
>
> Simple query
> SELECT rowid FROM TestTable WHERE (X BETWEEN 30000 AND 40000)
> Time: 330 ms, 110,000 rows
>
> Intersect query
> SELECT rowid FROM TestTable WHERE (X BETWEEN 30000 AND 40000) INTERSECT
> SELECT rowid FROM TestTable WHERE (Y BETWEEN 30000 AND 40000)
> Time:1800 ms, 10,000 rows
>
> and from my vfs stat the latter reads about 3M of data from this 44M base
> (so no full table scan)
>
> You say that your INNER JOIN QUERY faster? You probably have a different
> scheme, maybe that's the reason, but please let us know in this case
>
> Max
>
Max,
The three main differences between my table schema and yours above:
-- x and y are declared as floats
-- and the x and y values were not random values
What is your performance with a query that uses only a single index
without the INTERSECT function?
select rowid from TT where x >= ? and y <= ? and and x <= ? and y >= ?
Regards
Tim Romano
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
