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 slower, which is not what I
> wanted! What could be going on?
>
> I am calling SQLite from python 2.5 from fink useing the sqlite3
> module on OS X 10.5 with 2Gb of memory. My data base contains 32
> million lines of ocean hydrographic data, stored on disk, with the
> table created with the following statement:
>
<snip>
James --
I am not familiar with the Mac OSX OS ...
Have you checked the ulimit settings for the user doing the query ?
How large is your DB with and without the INDEX ?
I come up with a swag of ~ 2.2 GiB for the contents of the data in
your table, not counting the necessary overhead for a sqlite .db :
CREATE TABLE hydro
(
lat REAL, -- 1 - 8 Bytes
lon REAL, -- 2 - 8 Bytes
year INTEGER, -- 3 - 8 Bytes
month INTEGER, -- 4 - 8 Bytes
day INTEGER, -- 5 - 8 Bytes
time REAL, -- 6 - 8 Bytes
cast_id INTEGER, -- 7 - 8 Bytes
depth REAL, -- 8 - 8 Bytes
T REAL, -- 9 - 8 Bytes
S REAL, -- 10 - 8 Bytes
water_depth REAL -- 11 - 8 Bytes
) ; -- 88 Bytes * 32E6 = 2,816,000,000 Bytes
I do know when I exceed my ulimit settings, sqlite3 query performance suffers...
HTH.
-- kjh
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
