On Tue, Jun 28, 2011 at 8:18 PM, Jan Hudec <b...@ucw.cz> wrote: > On Tue, Jun 28, 2011 at 17:33:23 +0300, Lazarus 101 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, > ^^^^^^^^^^^^^^^^ Do file_ids repeat? If not, it should be > "integer > primary key". Than sqlite will alias the column to the "rowid" > column the table is internally ordered by for improved performance > looking up by file_id and saving some space (one fewer columns). >
yes, that was already marked as primary key but it was at the end of the create statement, that's why i forgot to mention it. > > name TEXT > > > > DATA > > file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE, > > data_type TEXT, > > If nothing else, you want to define integer identifiers for the data types > and use integer here. That will save you some space (reading from flash is > still performance bottleneck, especially if the flash is SD card) and some > unnecessarily costly string comparisons. In code, you'd obviously use > symbolic constants. > > > value TEXT > > Obviously you need index on DATA(file_id, data_type, value) > > Yes, all three columns. The first two will appear in your query, so have to > be first two having the result also included in the index saves time, > because > now everything can be obtained from the index and the table itself does not > have to be fetched. > I will try this, thanks. > > > data_type can be one of: > > (title, description, date_taken, latitude,longitude, duration, > > album,track_nr,genre, artist, year) > > Why this "hypernormalized" structure. When the set is fixed and there can > be > only one of each for each file, a big table with one column for each > attribute will do you much better service. > it's not fixed, for photos for example it's possible that we would want to add some other exif values, also some mp3 tags can have more then one value (artist, genre). > Just create FILES with > file_id integer primary key, > name text, > title text, > description text, > date_taken text, /* or integer if you decide to store timestamps instead > */ > latitude number, > longitude number, > duration number, > album text, > track_nr integer, > genre text, > artist text, > year integer > The files table has some other fields, I only mentioned those that I want in the result (there is also a path, favorite flag, parent_id, size,state etc. 10 columns in total), so I don't think a table with 20+ columns is a good idea, also there is the problem of adding new data types that would require altering the files table structure. And most of the time when I display the files list i don't need these specific media fields. > > > 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. > > Last but not least, "explain query plan" is your friend. If you prefix your > query with "explain query plan" and run it against the database (you can > use > the command-line shell or some management tool), sqlite will tell you what > tables it would read, in which order, using which indices and how big it > expects the result set to be. > > So create various indices and experiment with tweaking the query and look > what explain query plan tells you. Reading by primary key is fastest, > followed by covering index, noncovering index and temporary index or linear > search are worst (except join by temporary b-tree is near-optimal when you > are not filtering out anything). > > Sqlite before 3.7.4 didn't support temporary indices and temporary b-tree > joins, so it is much more critical to have good indices in older versions, > because they did happily regress to quadratic or worse complexity and that > would take ages to complete. > > Oh, and remember to remove the indices you end up not using to save space > and time needed to keep them up to date. > On my Android device sqlite version is 3.7.2 Thanks a lot, i will try the "explain query plan" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
