- Original Message
From: Christian Smith <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 9, 2006 8:38:51 AM
Subject: Re: [sqlite] indexes in memory
> chetana bhargav uttered:
> > Hi,
>
> > I have a question regrading indexes,
> >
> > When I open a connection,
> >
> > Will indexes be loaded into memory. If one of the tables in the DB, the
> > connection for which I have opened, has an index.
> > If, so is there any way to selectively load/unload that from memory.
>
>
> Indexes will be loaded into the cache as needed. The whole SQLite database
> is page based, and the cache caches the pages. The tables and indexes are
> implemented as page based btrees, with nodes represented by pages.
>
> The cache is unaware of the higher level structure of the btrees, and
> there is no way to selectively bring load/unload tables or indexes from
> memory. The page cache will manage itself on an LRU basis.
>
> > ...
> > Chetana.
> Christian
I found that when opening your connection, if you're about to do a lot of
operations it can be worth doing a "SELECT keyname FROM ... "over the whole
data to prepopulate the cache with the index data.
Even on pretty large datasets this only takes a few seconds and the following
operations will be much faster (and the overall time to complete the batch is
much smaller).
Nicolas
