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. > DATA > file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE, > data_type TEXT, > value TEXT > > data_type can be one of: > (title, description, date_taken, latitude,longitude, duration, > album,track_nr,genre, artist, year) > > as you've probably guessed this is used to store information about some > media files. > > Now, I want to select all music details in the following format > file_id | name | duration | genre | artist | description | album | track_nr > | year > > it's possible that a music from files does not have any data associated with > it, in this case i want the name to be in the result set > > This has to run as fast as possible. A left join between these tables is too > slow, for 10.000 entries it takes around 15 seconds just to navigate through > the cursor 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. 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. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users