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 table makes
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
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
From: Daniel Önnerby <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Adding index to table makes SELECT much slower. Why?
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Thursday, October 9, 2008, 6:18 AM
Have you tried running VACUUM and ANAYZE on t
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
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
>
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,
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
> >>
> >>
"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
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
13 matches
Mail list logo
