On Tue, Jun 28, 2011 at 5:48 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> > On 28 Jun 2011, at 3:33pm, Lazarus 101 wrote: > > > FILES > > file_id INTEGER NOT NULL, > > name TEXT > > I assume that SQLite has identified 'file_id' as its own 'rowid' column and > made in INDEX for it. > it's also marked as primary key > You should make an index on the columns 'file_id' and 'data_type' from the > 'DATA' table. This will allow it to be searched far more quickly. Your > command will be something like > > CREATE UNIQUE INDEX dfd ON data (file_id,data_type) > > Then do the above testing again. > tried that and it didn't help much > If it's still not fast enough, one possibility would be to create a TABLE > which actually reflects your data. Presumably one that reflects your layout > > file_id | name | duration | genre | artist | description | album | track_nr > | year > > You could use TRIGGERs to make this table change whenever your DATA table > changes. Or you could do it in software. Or you could abandon your DATA > table entirely. thanks, I will try this. Do you think that setting a trigger will have a performance impact on the insert statements? I receive the file list from the network in chunks of 1000 files and save them in the db and this also has to be fast (it's currently less then 500 millis per insert), or is it better to create this table after the initial sync and set the trigger only for future changes? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
