It was as simple as PRAGMA cache_size = 100000; <begin "Dr. Evil" voice; pinkie to lower lip.... > I want one *million* database disk pages. </end>
Thx, Doug On Tue, Apr 26, 2011 at 2:44 PM, Douglas Eck <d...@google.com> wrote: > Hi all, > I have a table like this; > CREATE TABLE entity ( > id BIGINT NOT NULL, > a_id VARCHAR(64), > b_id VARCHAR(64), > path VARCHAR, > .... < more fields>, > created_at TIMESTAMP, > PRIMARY KEY (id) > ); > > 3.1M records. > > When I create an index for a_id it is very slow to create (many hours). > > I'm doing it by writing these lines to a text file: > PRAGMA cache_size = 50000; > PRAGMA synchronous = OFF; > CREATE INDEX ix_entity_a_id on entity(a_id); > > and then doing > sqlite3 my.db < my_text_file > > Can anyone suggest ways to speed this up? > > Some specific questions: > Is VARCHAR(64) slower to index than other types? > These values should be unique. Is it faster to index with UNIQUE? > All strings in this field are prefixed with the same value. Could this > be messing up hashing? > > Best, > Doug Eck > -- Douglas Eck Research Scientist, Google Areas: Music and Machine Learning _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users