Wilson, Ron P schrieb:
I'm not a guru yet, but I think you are not using the latlon index in
your query. Perhaps if you index on lat and lon separately your query
will use those indices. I think the lines below indicate using the
indices on class_dds and rowid.
Thanks to everyone who
Can someone tell me what kind of performance one is to expect from a
40Mb Sqlite database like the one I have?
if you put it on a floppy and throw it out of the window it fall at 9.8 m/s
...
Can you give me some row of your db (also fake data are ok) so I try to
populate a db with 840k row
Can you give me some row of your db (also fake data are ok) so I try to
populate a db with 840k row and test your query on my machine ...
You can either take these rows here:
Pietraporzio|5|-1|7.032936|44.345913
Sambuco|5|-1|7.081367|44.33763
Le Pra|6|-1|6.88|44.316667
There is virtually no difference in using indices or not in my query.
I also tried to reformulate my statement in order not to use BETWEEN but
a sandwiched and statement:
SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWEEN
6.765103 and 7.089129) AND (latitude_DDS BETWEEN
can you post those rows with
.mode insert
so I can do a fast try ?
Tnx.
--
[image: Just A Little Bit Of
Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).
2008/6/4 Christophe Leske
Federico Granata schrieb:
can you post those rows with
.mode insert
so I can do a fast try ?
INSERT INTO table VALUES('Pietraporzio',5,-1,7.032936,44.345913);
INSERT INTO table VALUES('Sambuco',5,-1,7.081367,44.33763);
INSERT INTO table VALUES('Le Pra',6,-1,6.88,44.316667);
INSERT INTO
Christophe Leske wrote:
There is virtually no difference in using indices or not in my query.
I also tried to reformulate my statement in order not to use BETWEEN but
a sandwiched and statement:
SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWEEN
6.765103 and 7.089129) AND
Question, have you tried an index on class_dds, longitude_DDS, and
latitude_DDS?
CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS);
Since all three fields are used in the query, I am curious if that would
help in any way.
Doesn´t do anything, there is something
Christophe Leske schrieb:
Question, have you tried an index on class_dds, longitude_DDS, and
latitude_DDS?
CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS);
Since all three fields are used in the query, I am curious if that would
help in any way.
Doesn´t
class_dds has a maximum value of 6, so there where-clause class_dds11
is totally unecessary - if i ditch this part, the response time is
coming down to 900ms from 2700ms for my request.
I will now time again.
Some new timings - i basically got it. What I find to be weird is that
just
Hi Christophe,
Regarding: What I find to be weird is that just ONE index seems to
yield the same results as several fields indexed:
Perhaps you're using this already, but prefixing your SELECT with
EXPLAIN QUERY PLAN will quickly identify exactly which, if any
indicies are used. It's a much
On Wed, Jun 4, 2008 at 7:12 AM, Christophe Leske [EMAIL PROTECTED] wrote:
Wilson, Ron P schrieb:
I'm not a guru yet, but I think you are not using the latlon index in
your query. Perhaps if you index on lat and lon separately your query
will use those indices. I think the lines below
Electronics, 434.455.6453
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske
Sent: Tuesday, June 03, 2008 1:27 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How to speed up my queries?
Hi,
i am a new member of this list
Hi,
i am a new member of this list and interested in speeding up my sqlite
queries.
I am using SQlite in a 3d environment which is close to Google Earth or
Nasa WorldWind.
We have a city database that is being queried regurlarly depending on
the lat/long position of the viewport in order to
Wilson, Ron P schrieb:
Hi Christophe,
1. Please give us an example query. SELECT * FROM Cities where
LONGITUDE_DDS=? AND LATITUDE_DDS=?
2. Paste in the EXPLAIN results from the command line tool.
3. Is the database file local or are you accessing it over a network?
Hi,
the database
On Jun 3, 2008, at 7:27 PM, Christophe Leske wrote:
i am a new member of this list and interested in speeding up my
sqlite queries.
There are no magic bullets, but The SQLite Query Optimizer Overview
is a good read:
http://www.sqlite.org/optoverview.html
As well as Query Plans:
On Tue, Jun 03, 2008 at 07:56:11PM +0200, Christophe Leske scratched on the
wall:
A typical query that causes problems would be:
SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWEEN
6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and
44.424779) ORDER BY class_dds
Discussion of SQLite Database
Subject: Re: [sqlite] How to speed up my queries?
Wilson, Ron P schrieb:
Hi Christophe,
1. Please give us an example query. SELECT * FROM Cities where
LONGITUDE_DDS=? AND LATITUDE_DDS=?
2. Paste in the EXPLAIN results from the command line tool.
3. Is the database
Leske
Sent: Tuesday, June 03, 2008 1:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How to speed up my queries?
Wilson, Ron P schrieb:
Hi Christophe,
1. Please give us an example query. SELECT * FROM Cities where
LONGITUDE_DDS=? AND LATITUDE_DDS=?
2. Paste
On Tue, Jun 3, 2008 at 1:27 PM, Christophe Leske [EMAIL PROTECTED] wrote:
Hi,
i am a new member of this list and interested in speeding up my sqlite
queries.
I am using SQlite in a 3d environment which is close to Google Earth or
Nasa WorldWind.
We have a city database that is being
-- Even if you only go down to 1'-by-1' granularity, you've divided the
world into 64,800 blocks. Assuming that your 840K cities are all over the
globe, and that about 70% of Earth is covered by water, that means that only
about 20,000 blocks would actually have cities in them. But with 840K
On Jun 3, 2008, at 10:27 AM, Christophe Leske wrote:
We have a city database that is being queried regurlarly depending on
the lat/long position of the viewport in order to show city names and
labels.
SQLite has an optional R-Tree engine. The R-Tree is a new addition
and has not appeared
On 6/3/08, D. Richard Hipp [EMAIL PROTECTED] wrote:
On Jun 3, 2008, at 10:27 AM, Christophe Leske wrote:
We have a city database that is being queried regurlarly depending on
the lat/long position of the viewport in order to show city names and
labels.
SQLite has an optional
23 matches
Mail list logo
