From seth at userprimary.net  Tue Oct 23 06:33:07 2007
From: seth at userprimary.net (Seth Falcon)
Date: Mon, 22 Oct 2007 21:33:07 -0700
Subject: [Rd] RSQLite indexing
In-Reply-To: <471D2C5A.5000608@vanderbilt.edu> (Jeffrey Horner's message of
	"Mon\, 22 Oct 2007 18\:03\:54 -0500")
References: <Pine.LNX.4.43.0710221454390.27183@hymn12.u.washington.edu>
	<471D2C5A.5000608@vanderbilt.edu>
Message-ID: <m2lk9uo3gc.fsf@userprimary.net>

Jeffrey Horner <jeff.horner at vanderbilt.edu> writes:

> Thomas Lumley wrote on 10/22/2007 04:54 PM:
>> I am trying to use RSQLite for storing data and  I need to create indexes on 
>> two variables in the table. It appears from searching the web that the CREATE 
>> INDEX operation in SQLite is relatively slow for large files, and this has been 
>> my experience as well.

What is your schema?  In particular, are things that are integers or
floats being stored that way in SQLite?

I believe the annotation data packages via AnnotationDbi are using
cache_size=64000 and synchronous=0 and that this was determined by a
handful of experiments on typical annotation dbs.

Columns with few levels may not benefit from an index.  See this
thread:

http://thread.gmane.org/gmane.comp.db.sqlite.general/23683/focus=23693

But your column with many levels should suffer this problem :-)

+ seth

-- 
Seth Falcon | seth at userprimary.net | blog: http://userprimary.net/user/



[Rd] RSQLite indexing
Seth Falcon