[sqlite] [BUG; 3.7.13] ANALYZE leads to a wrong query plan for RTrees

[skywind mailing lists]

This is an example that the ANALYZE command leads to a wrong query plan for 
RTrees:

CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude);
CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude, 
FromLongitude,TillLongitude);
INSERT INTO A VALUES(1,0,0,0);
INSERT INTO A VALUES(2,1,1,1);
INSERT INTO B VALUES(1,0,0,0,0);
INSERT INTO B VALUES(2,1,1,1,1);

Without an analyze command the query plan seems to be OK:
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND 
(B.FromLongitude > 5) AND (B.TillLongitude < 10);
0|0|1|SCAN TABLE B VIRTUAL TABLE INDEX 2:EcCd (~0 rows)
0|1|0|SEARCH TABLE A USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

After running the ANALYZE command the query plan has changed and the result is 
a worse query plan than before:
ANALYZE;
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND 
(B.FromLongitude > 5) AND (B.TillLongitude < 10);
0|0|0|SCAN TABLE A (~2 rows)
0|1|1|SCAN TABLE B VIRTUAL TABLE INDEX 1: (~0 rows)

Regards,
Hartwig

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

[Petite Abeille]

On Dec 9, 2013, at 8:01 PM, Warren Young  wrote:

>> I remember reading an essay by a user of controlled substances
> 
> Your next reading assignment is a book[3] on a functional programming 
> language,

So… do you make your functions wear a purity ring? To keep them, hmmm, chaste?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RTrees and query speed

[skywind mailing lists]

Am 09.12.2013 um 10:06 schrieb Clemens Ladisch :

> skywind mailing lists wrote:
>> Assume I have the following tables:
>> 
>> CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude);
>> CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude, 
>> FromLongitude,TillLongitude);
>> 
>> According to the RTree documentation this query should be fast (demo_data / 
>> demo_index example):
>> 
>> SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND (FromLongitude > 5) AND 
>> (TillLongitude < 10);
>> 
>> Actually, the query is pretty slow.
> 
> According to the EXPLAIN QUERY PLAN output (),
> it is fast:
> 
> 0|0|1|SCAN TABLE B VIRTUAL TABLE INDEX 2:EcCd
> 0|1|0|SEARCH TABLE A USING INTEGER PRIMARY KEY (rowid=?)
> 
> To you get the same output for EXPLAIN QUERY PLAN?
> If not, what SQLite version are you using?
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Hi Clemens,

I am using SQLite 3.7.13.

If I do not run ANALYZE my query plan is the same (I rebuild the tables). BUT 
my query plan is different after ANALYZE:

0|0|0|SCAN TABLE A (~74067 rows)
0|1|1|SCAN TABLE B VIRTUAL TABLE INDEX 1: (~0 rows)

The problem seems to be (~0 rows) for TABLE B. There are definitely 74067 rows 
in table B. Therefore, I conclude that ANALYZE is not able to analyze RTree 
tables correctly and therefore the wrong query plan is chosen.

Regards,
Hartwig

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Last Modified" file attribute is not updated

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/12/13 10:30, Felipe Farinon wrote:
> I'm sorry to repost, but I just want to confirm that there is no
> interest in fixing this, so that I can handle this with a workaround in
> my application.

Note that the effect will be to make SQLite slower since it would need
extra system calls at various points to do metadata updates.  The most
accurate would be to update the timestamp on every write!

Looking at the last modified date is also not the most effective way of
detecting things.  You can use the filesystem archive flag to detect
changes since you last looked at the file.

You can use a trigger internally at the SQL level to track changes in a
meaningful way too.

Finally you can disable the use of memory mapping - have a look at the
Windows VFS.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)

iEYEARECAAYFAlKmLooACgkQmOOfHg372QQ5YQCfSSIJHA7cWQQ9X1IpHGkgQnr+
K3AAn0w1Wl1OIfNhR5n0zDCMHDlIqyqD
=GUnl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

[Warren Young]

On 12/7/2013 12:53, James K. Lowden wrote:

On Thu, 05 Dec 2013 17:52:47 -0700
Warren Young  wrote:


To prove my point, I decided to divide the SQLite commands[1] into
those that modify the DB and those that do not:


Oh, let me help you out here: these aren't functions.


I was careful to call them commands, and to treat what SQL calls 
"functions" separately.


Nevertheless, I think you're trying to draw a dictionary-based line here 
instead of looking at fundamental concepts.  A proper mathematical 
function takes N arguments and returns a single constant result for 
those arguments.  If you have a static SQLite DB file, any SELECT 
statement against it involving only tables and the pure SQL functions 
qualifies as a pure function itself.


The point of this exercise was to dig down to this conceptual level, 
bypassing the fuzzy terminology.


SQL is a mixed bag of true functions and non-functional [1] elements. 
This thread is about one confusion that can result when these two 
aspects of SQL intermix[2] in unexpected ways.



I remember reading an essay by a user of controlled substances


Your next reading assignment is a book[3] on a functional programming 
language, preferably one with immutable-by-default values.  Haskell is 
the current hotness, but Erlang would work just as well.


There are less pure FP languages that can teach the same lessons, if you 
diligently avoid the impure bits: the ML family[4], Scala, Scheme...


The rest of your post I answered indirectly in my reply to your other 
message in this thread.





Footnotes:

[1] In the mathematical sense.  I.e. not meaning "broken".

[2] e.g. "SELECT ... date('now')"

[3] Free online FP books:

http://learnyouahaskell.com/chapters
http://learnyousomeerlang.com/content
http://ocaml.org/learn/books.html
https://en.wikibooks.org/wiki/F_Sharp_Programming
http://www.scala-lang.org/documentation/books.html
http://www.scheme.com/tspl4/
https://mitpress.mit.edu/sicp/

[4] OCaml and F# are the most-used flavors of ML in practice currently. 
 Academia still has a lot of Standard ML holdouts.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Last Modified" file attribute is not updated

[Felipe Farinon]

I'm sorry to repost, but I just want to confirm that there is no 
interest in fixing this, so that I can handle this with a workaround in 
my application.


Em 04/12/2013 15:42, Felipe Farinon escreveu:

I'm using Windows 7, sqlite 3.7.17 compiled with a VS 2010.

When writing in a sqlite database configured with 
SQLITE_CONFIG_MMAP_SIZE, the database file atribute "Last Modified" 
isnt updated. According to 'MapViewOfFile' documentation [1],


"When modifying a file through a mapped view, the last modification 
timestamp may not be updated automatically. If required, the caller 
should use SetFileTime to set the timestamp."


I could create a workaround to fix this inside my application, but 
maybe it's sqlite responsibility to call SetFileTime after updating a 
database content.


[1] 
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366761(v=vs.85).aspx 
 


___
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

Re: [sqlite] sqlite download for 64 bit

[j. merrill]

Use the 32-bit DLL. If you use that, your application will run on both 32-
and 64-bit versions of Windows. If you use the 64-bit DLL, it will only work
on 64-bit versions of Windows.


Krishna Chaitanya Konduru wrote
> at the sqlite download page there is download for win 32 x86 what abut
> 64bit os.. would the same appllication runon both 32 and 64 bit os??





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite-download-for-64-bit-tp72839p72850.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

[Warren Young]

On 12/7/2013 12:15, James K. Lowden wrote:

On Wed, 04 Dec 2013 12:04:07 -0700
Warren Young  wrote:


Determinism is a property of a function; there is no such
thing as a function that is sometimes deterministic and sometimes
not.


databases are about as far from side-effect-free as you can get.


I'm not sure what you're referring to.


I think your sense of the term "side effect" comes from the everyday 
use, which is most influenced by medical side effects.  i.e., something 
bad and unintended.


The term means something rather different in CS:

https://en.wikipedia.org/wiki/Side_effect_%28computer_science%29

Specifically here, I mean that most SQL statements other than SELECT 
modify global state: the SQLite DB file.  Any statement that modifies 
the DB file has the potential to change the result from *any* SQL 
statement, including SELECT.


Example:

SELECT * FROM foo WHERE id=42;
UPDATE foo SET bar='qux' where id=42;
SELECT * FROM foo WHERE id=42;

The first and third statements return different results, even though 
they are side effect free, because UPDATE is not side effect free.


Consider also that the UPDATE could come from another process, at an 
indeterminate time.  This is why concerns over side effects -- in the CS 
sense -- matter.


SQLite offers many ways to *control* this indeterminacy, features 
generally grouped under the acronym ACID, but you can't say "DBMS X is 
ACID compliant therefore it will never surprise me with unexpected results."

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT statement failure

[Simon Slavin]

On 9 Dec 2013, at 4:12pm, RSmith  wrote:

> On 2013/12/09 15:32, Simon Slavin wrote:
>> Second, multiplying by 100 and defining columns as INTEGER to store money to 
>> two places /is/ the correct solution.  This means that arithmetic will 
>> automatically be done to two places.  You will have to test whether results 
>> are rounded or truncated when you do tax calculations but better still would 
>> be to explicitly use round() when any dividing is done.
> 
> I cannot agree with this - maybe if you are storing a simple personal budget 
> on your home PC, but in any real-world financial institution or corporate 
> finance system and even the corner-shop up your street, those milli-dollars 
> (if you will) behind the cents can amount to substantial amounts over some 
> transactions and/or time and should not ever be lost to rounding.  (In most 
> large bank-systems, the accumulated transactional amounts less than 1 cent 
> amounts to several million per month)

Actually, I used to work with banking software and I can tell you how it's done 
for real.  And the answer is that they really do use integers for the smallest 
tradable unit of currency.  So now days, for both the US dollar and the GB 
pound they would use hundredths, just as stated above.  Back when I did it Lira 
was done with four places (ten thousandths) because by law it was theoretically 
possibly to trade a ten thousandth of a Lira.  And when the half-penny existed 
in Britain we had to use two-hundredths of a pound !

The reason integers were used was that banks can't be accused of snaffling the 
fractions.  So all add/subtract/multiply is done with the integers.  And 
division must always have an explicit round() or truncate() in to make it clear 
what they intended the program to do.  Some of the apps I worked with had a 
hacked compiler which had had automatic coercion removed so the programmer 
couldn't forget to use coercion functions.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT statement failure

[j . merrill]

If floating-point data is involved (common when values are stored as dollars 
and cents, e.g. 2.79 for 2 dollars 79 cents) you cannot use "=" for a 
comparison like this, because of small differences caused by the lack of exact 
representation for many floating-point numbers. See

http://en.wikipedia.org/wiki/Floating_point#Minimizing_the_effect_of_accuracy_problems

and search for "The use of the equality test". (This is a very common issue 
when working with floating-point data. A lot of software stores money amounts 
as an integer number of pennies to avoid it.)

The easiest solution is to subtract the two values in question, take the 
absolute value, and compare the difference to something reflecting the maximum 
difference you will allow -- something like

SELECT * FROM orders_tbl WHERE 0.01 <=
abs((ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)-subtotal);

(To reverse this to an "it's equal" test, change <= to > rather than != to =.) 
Using a value slightly less than 0.01 (e.g. 0.008) might prevent truly fringe 
cases from giving the wrong answer.

J. Merrill
 

-Original Message-
Date: Fri, 06 Dec 2013 13:13:54 -0500
From: Scott Slater 
To: sqlite-users@sqlite.org
Subject: [sqlite] SELECT statement failure

Hello,

I had a customer contact me that a report was randomly "missing data" 
and have tracked down the problem to a single SQL query.  the problem is 
that some items, that logically match the WHERE clause don't get selected.

I have put together a reverse logic of that statement below by changing 
the = to != .  I have attached a very small subset of this client's data 
that demonstrates the problem.  Using the attached file which contains 4 
orders none of which should get selected using the statement below;

SELECT * FROM orders_tbl WHERE 
(ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)!=subtotal;

However, record #1 gets selected.  If you change the != back to an =, 
then you get the remaining 3.

If you manually look through the relevant column data all 4 orders are 
logically correct, and should be treated in the same manner by the 
select statements.

I have tested this on Windows machines and posted it to a forum where 
another user was able to demonstrate the same issue.  I have tried the 
windows command shell version (sqlite3.exe / 
sqlite-shell-win32-x86-3080200.zip) as well as the sqlite3.dll / 
sqlite-dll-win32-x86-3080100.zip and an earlier (not sure which) version 
of the dll.  I have not tested on any other operating systems.


Regards,

Scott Slater
Summit Computer Networks, Inc.
(866) 922-9690  Ext. 7701
(724) 779-6390  Ext. 7701
sslat...@summitcn.com


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [ANNOUNCEMENT] Updated: sqlite3-3.8.2-2 for Cygwin/Cygwin64

[Jan Nijtmans]

SQLite is a software library that implements a self-contained,
serverless, zero-configuration, transactional SQL database engine

Changes since 3.8.1-1
=
* Updated to upstream 3.8.2 release. Main features:
  * Added support for WITHOUT ROWID tables.
  * Extend the maximum path length from 512 to
4096 for all VFS's except "win32".
  * New VFS "unix-namedsem".
  * Since the dll is compiled with
 -DSQLITE_ENABLE_EXPLAIN_COMMENTS,
 the .expain command now gives more useful
 output than in earlier releases.

--
Jan Nijtmans
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite download for 64 bit

[RSmith]

I think the OP might mean the DLL downloaded from the site - which is W32, I don't think this can be linked into a 64-bit 
application... can it?  (I haven't attempted it).


Would the powers that be terribly mind adding a 64-bit DLL to the download list?


On 2013/12/09 16:24, Kees Nuyt wrote:

On Mon, 9 Dec 2013 17:48:34 +0530, Krishna Chaitanya Konduru 
 wrote:


hi
at the sqlite download page there is download for win 32 x86 what abut
64bit os.. would the same appllication runon both 32 and 64 bit os?? I am a
total newbie

SQLite compiled for 32 bit works fine on 32-bit and 64-bit Windows OS.

Only for very large databases you may need SQLite compiled for 64 bit.
Typically, that is not something a newbie would do.



Regards
Krishna


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite download for 64 bit

[Kees Nuyt]

On Mon, 9 Dec 2013 17:48:34 +0530, Krishna Chaitanya Konduru 
 wrote:

>hi
>at the sqlite download page there is download for win 32 x86 what abut
>64bit os.. would the same appllication runon both 32 and 64 bit os?? I am a
>total newbie

SQLite compiled for 32 bit works fine on 32-bit and 64-bit Windows OS.

Only for very large databases you may need SQLite compiled for 64 bit.
Typically, that is not something a newbie would do.


>Regards
>Krishna

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT statement failure

[Simon Slavin]

On 8 Dec 2013, at 10:21pm, MikeD  wrote:

> The simple solution is use INTEGER and multiple by .01, but
> extremely interested in how to correctly process money with 2 decimal
> positions.

First, never do this:

> CREATE TABLE IF NOT EXISTS T1(F1,F2,F3,F4,SUBTOTAL);


Always define your column types.  In your case you'd be using either INTEGER or 
REAL.

Second, multiplying by 100 and defining columns as INTEGER to store money to 
two places /is/ the correct solution.  This means that arithmetic will 
automatically be done to two places.  You will have to test whether results are 
rounded or truncated when you do tax calculations but better still would be to 
explicitly use round() when any dividing is done.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT statement failure

[MikeD]

drop table if exists orders_tbl;
create table if not exists
orders_tbl(ord_total,discount,tax1,tax2,tax3,tax4,delivery_tax,delivery_fee,subtotal);
insert into orders_tbl values(38.55, 0, 2.42, 0, 0, 0, .1, 1.5, 34.53);
insert into orders_tbl values(3855, 0, 242, 0, 0, 0, 10, 150, 3453);

select *,'(rounding error)' from orders_tbl where
(ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)!=subtotal; 
select *, '(used round)' from orders_tbl where
round(ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee,2)=subtotal;
 
select *, '(multiple results by .01)' from orders_tbl where
(ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)=subtotal; 

result:
--
38.55  0  2.42  0  0  0  0.1  1.5  34.53  (rounding error)  
--
38.55  0  2.42  0  0  0  0.1  1.5  34.53  (used round)  
3855  0  242  0  0  0  10  150  3453  (used round)  
--
3855  0  242  0  0  0  10  150  3453  (multiple results by .01)  




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SELECT-statement-failure-tp72814p72837.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite download for 64 bit

[Krishna Chaitanya Konduru]

hi
at the sqlite download page there is download for win 32 x86 what abut
64bit os.. would the same appllication runon both 32 and 64 bit os?? I am a
total newbie
Regards
Krishna
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT statement failure

[MikeD]

Correction:
USING REAL:
DROP TABLE IF EXISTS T1; 
CREATE TABLE IF NOT EXISTS T1(F1,F2,F3,F4,SUBTOTAL); 
INSERT INTO T1 VALUES(38.55,2.42,.1,1.5,34.53);
SELECT * FROM T1 WHERE ROUND(F1-F2-F3-F4,2)=SUBTOTAL; 




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SELECT-statement-failure-tp72814p72829.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite_version

[MikeD]

This is how it is done in code:
select sqlite_version()



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SQLite-version-tp72127p72830.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT statement failure

[MikeD]

This will cause a rounding error so using INTEGER * .01 in second example.

USING REAL:
DROP TABLE IF EXISTS T1;
CREATE TABLE IF NOT EXISTS T1(F1,F2,F3,F4,SUBTOTAL);
INSERT INTO ORDERS_TBL VALUES(38.55,0,2.42,0,0,0,.1,1.5,34.53)
SELECT * FROM orders_tbl WHERE ROUND(F1-F2-F3-F4,2)=SUBTOTAL;

Using INTEGER:
DROP TABLE IF EXISTS T1;
CREATE TABLE IF NOT EXISTS T1(F1,F2,F3,F4,SUBTOTAL);
INSERT INTO T1 VALUES(3855,242,10,150,3453);
SELECT * FROM T1 WHERE F1-F2-F3-F4 = SUBTOTAL;


This brings up the internal representation of a decimal on either side of an
equal sign.
IF  X = Y
Does this mean it is necessary to use IF ROUND(X,2) = ROUND(Y,2)?

IF A = B + C
Should this use:
IF ROUND(A,2) = ROUND(A+B)
or
IF ROUND(A,2) = ROUND(A,2) + ROUND(B,2)?

The simple solution is use INTEGER and multiple by .01, but
extremely interested in how to correctly process money with 2 decimal
positions.

Thank you,
Mike






--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SELECT-statement-failure-tp72814p72828.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RTrees and query speed

[Clemens Ladisch]

skywind mailing lists wrote:
> Assume I have the following tables:
>
> CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude);
> CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude, 
> FromLongitude,TillLongitude);
>
> According to the RTree documentation this query should be fast (demo_data / 
> demo_index example):
>
> SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND (FromLongitude > 5) AND 
> (TillLongitude < 10);
>
> Actually, the query is pretty slow.

According to the EXPLAIN QUERY PLAN output (),
it is fast:

0|0|1|SCAN TABLE B VIRTUAL TABLE INDEX 2:EcCd
0|1|0|SEARCH TABLE A USING INTEGER PRIMARY KEY (rowid=?)

To you get the same output for EXPLAIN QUERY PLAN?
If not, what SQLite version are you using?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users