Re: [Rd] RSQLite indexing

From: Seth Falcon <seth_at_userprimary.net>
Date: Mon, 22 Oct 2007 21:33:07 -0700

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@userprimary.net | blog: http://userprimary.net/user/

______________________________________________
R-devel_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-devel
Received on Tue 23 Oct 2007 - 04:36:07 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Thu 25 Oct 2007 - 11:37:11 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-devel. Please read the posting guide before posting to the list.