On 2/23/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
P Kishor wrote:
>
>
> Most of the time I am looking at one site, so there is a speed gain by
> not plowing through other sites' data. This is what is causing me to
> pause before I rush forward.
>
If you have an index on the readings table by site_id (and perhaps
timestamp) sqlite will use the index to go directly to that site's
records and will not plow through all the other site's data. Adding a
second field, like the timestamp, to the index will let you do fast
searches or selections based on time within a single site as well.
P.S. I would recommend keeping the integer primary key ID fields.
Because sqlite uses them for the rowid (which each record in each table
must have anyway) they cost you nothing and make joins more direct and
obvious.
HTH
yes, Dennis, this does help. Thanks. I liked John Stanton's suggestion
as well, very elegant, but I think your approach is more scalable, as
once the prototype is done and working well, it will be migrated to
PostGres. A question -- you say
If you have an index on the readings table by site_id (and perhaps
timestamp) sqlite will use the index to go directly to that site's
records and will not plow through all the other site's data. Adding a
second field, like the timestamp, to the index will let you do fast
well, site_id is a PRIMARY KEY, so it is automatically indexed. I am
not creating another index on it. I am indexing timestamp, but are you
suggesting a compound site,timestamp index?
Also, if I have two cols a and b, and I create separate indexes on a
and b, would it hurt if I also have an additional compound index on
a,b? In other words, does SQLite have the smarts to know when to use
what?
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
