Ken and others-
Thanks for your help. I have put a copy of the data and a python
script that exercise the data via sqlite3 at
http://oxbow.sr.unh.edu/nogoogle/wod/ for you to play with and see if these
results hold on other architectures. The script is straightforward, and you
can easily cu
Sure post it data and the website for download.
I'd like to see if it's reproducible on other systems.
Thanks,
Ken
--- On Thu, 10/9/08, James Pringle <[EMAIL PROTECTED]> wrote:
> From: James Pringle <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] Adding index to ta
SELECT lat, lon, depth FROM hydro
WHERE depth>= 49.01 AND depth <=50.99;
The SELECT may be faster still if you use this index instead:
CREATE INDEX hydro_indx2 ON hydro (depth, lat, lon);
as the query can find all the information in the index without
hitting the pages of the main table.
Hi all-
Thanks for your many suggestions. I have tried many of your suggestions,
and found the following:
1. If I create the database with depth ordered from least to greatest,
and then create an index, the searches run an order of magnitude faster than
without an index. I.e. the s
From: Daniel Önnerby <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Adding index to table makes SELECT much slower. Why?
To: "General Discussion of SQLite Database"
Date: Thursday, October 9, 2008, 6:18 AM
Have you tried running VACUUM and ANAYZE on the indexed table?
Not that I think i
On 10/08/2008 05:50 PM, James Pringle wrote:
> Hi-
>
> I am new to sqlite, and am having a puzzling problem. I have read
> that adding an INDEX to a sqlite table can never make a SELECT
> statement slower. However, in my case, it seems to be making the
> select statement an order of magnitude
Have you tried running VACUUM and ANAYZE on the indexed table?
Not that I think it will matter, but who knows :)
Daniel
James Pringle wrote:
> Hi-
>
> I am new to sqlite, and am having a puzzling problem. I have read
> that adding an INDEX to a sqlite table can never make a SELECT
> statement
When used with the index, the query may be producing too
many random (slow) disk seeks into your 2GB+ database table.
Increasing the page cache substantially may help.
But if the rows of the main table were accessed in order it might
also reduce the number of page seeks. Out of curiosity, without
On 10/8/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> "P Kishor" <[EMAIL PROTECTED]> wrote in
> message
>
> news:[EMAIL PROTECTED]
>
> > Imagine a db where the column 'foo' that you are querying against has
> > a different value for every single row. What possible good an index
> > would do?
>
"P Kishor" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> Imagine a db where the column 'foo' that you are querying against has
> a different value for every single row. What possible good an index
> would do?
It would let you find a row with a specific value in this column in logN
On 10/8/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> "P Kishor" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>
> > On 10/8/08, James Pringle <[EMAIL PROTECTED]>
> > wrote:
>
> >> SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND depth
> >> <= 50.99
> >>
> >> It
"P Kishor" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> On 10/8/08, James Pringle <[EMAIL PROTECTED]>
> wrote:
>> SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND depth
>> <= 50.99
>>
>> It selects and returns the records in 82 seconds. I wished to
>> improve on thi
On 10/8/08, James Pringle <[EMAIL PROTECTED]> wrote:
> Hi-
>
> I am new to sqlite, and am having a puzzling problem. I have read
> that adding an INDEX to a sqlite table can never make a SELECT
> statement slower. However, in my case, it seems to be making the
> select statement an order of
James,
I'm not sure I can answer your questions about speed issues other than
to suggest the you read up on the rtree index which is designed for this
type of query and should be extremely fast.
-Steve
James Pringle wrote:
> Hi-
>
> I am new to sqlite, and am having a puzzling problem. I h
Hi-
I am new to sqlite, and am having a puzzling problem. I have read
that adding an INDEX to a sqlite table can never make a SELECT
statement slower. However, in my case, it seems to be making the
select statement an order of magnitude slower, which is not what I
wanted! What could be going
15 matches
Mail list logo