[Sorry for the blast from the past.]
I think this class of problem does also happen on fts, there was a
thread on August 7 on sqlite-dev about it. Unfortunately, I don't see
any open-access web-mirrors of that list to refer to, but here's a
members-only ref:
see sqlite3_index_info.estimatedCost
http://www.sqlite.org/cvstrac/chngview?cn=5649
AFAICT, when you have a join where one table has a good index, the
virtual table cannot signal that it has an even better index. I could
not follow the index-selection logic well enough to have any
I'll have to look again. I was sure I had mucked with estimatedCost
and found that it wasn't doing the trick for what I meant to do - but
somehow the context has swapped out of my short-term memory, so I'll
have to recreate it before I can say why this was the case.
-scott
On Wed, Sep 10, 2008
Am 18.08.2008 um 21:32 schrieb Dennis Cote:
Thomas Sailer wrote:
Interestingly, the original query is extremely compute-bound, there
is
almost no disk activity!
Looking at the output opcodes from the queries, I can't see any
significant difference. Though I have to admit I'm by far no
I have a database with the following schema:
CREATE TABLE mapelements (ID INTEGER PRIMARY KEY NOT NULL, TYPECODE
INTEGER,NAME TEXT,LAT INTEGER,LON INTEGER,SWLAT INTEGER,SWLON INTEGER,NELAT
INTEGER,NELON INTEGER,POLY BLOB,TILE INTEGER);
CREATE VIRTUAL TABLE mapelements_rtree USING
On Aug 18, 2008, at 12:21 PM, Thomas Sailer wrote:
The following query, however, takes a long time (almost half a
minute):
select * from mapelements,mapelements_rtree where
mapelements_rtree.NELAT=7900 and
mapelements_rtree.SWLAT=8000 and
mapelements_rtree.NELON=7900
On Mon, 2008-08-18 at 12:28 -0400, D. Richard Hipp wrote:
Please try changing the last term as shown below (add a + before the
r-tree ID column):
mapelements.ID = +mapelements_rtree.ID
Thanks a lot for your quick answer! Unfortunately, it didn't help.
What's interesting is that the
Hello!
В сообщении от Monday 18 August 2008 20:21:04 Thomas Sailer написал(а):
The following query is very quick, it returns 20 rows within a small
fraction of a second: select * from mapelements_rtree where NELAT=7900
and SWLAT=8000 and NELON=7900 and SWLON=8000;
The
Thomas Sailer wrote:
The following query is very quick, it returns 20 rows within a small fraction
of a second:
select * from mapelements_rtree where NELAT=7900 and SWLAT=8000 and
NELON=7900 and SWLON=8000;
The following query, however, takes a long time (almost half a
On Mon, 2008-08-18 at 11:16 -0600, Dennis Cote wrote:
Does this query run faster?
select * from mapelements
where ID in
(
select ID from mapelements_rtree
where mapelements_rtree.NELAT=7900
and mapelements_rtree.SWLAT=8000
and
Thomas Sailer wrote:
Interestingly, the original query is extremely compute-bound, there is
almost no disk activity!
Looking at the output opcodes from the queries, I can't see any
significant difference. Though I have to admit I'm by far no expert in
vmdb opcodes...
You can use
11 matches
Mail list logo