> 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, if I add a where clause selecting only one kind of data then it > reduces to less than 5 seconds which is acceptable.
What kind of selects did you do here? Generally in my opinion if you want to get all data as one table with one file per row you shouldn't do that using just SELECT statement. You should get raw data with SELECT and then transform it into the necessary table format in your application. Pavel On Tue, Jun 28, 2011 at 10:33 AM, Lazarus 101 <lazarus...@gmail.com> wrote: > Hi guys, i'm working on an Android app and using sqlite to store some data > and i need some help with a query. > > I have the following table structure: > > FILES > file_id INTEGER NOT NULL, > name TEXT > > 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, if I add a where clause selecting only one kind of data then it > reduces to less than 5 seconds which is acceptable. > > Thanks a lot for any input. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
