Method 3, normalization, is the right route but I think the implementation needs a little more work. First don't store both Artist ID and Artist Name in the Music table--only store the ID. This goes for AlbumID/Name and GenreID/Name as well. Then reorder the columns to put the integers first in the field order for the Music table, especially the foreign keys. Finally, create appropriate indexes. Keep in mind that SQLite will only use one index per table in a query so most likely you'll want a compound index that indexes multiple foreign key fields at once.
For queries that used to be "select distinct AlbumName from music" now just query "select AlbumName from Albums" and you'll see a huge performance increase (notice they don't need the music table at all). Also, I would suggest using consistent names across tables. So instead of naming all your id fields "id" use something more specific like "ArtistID" and "AlbumID" that way the same field name is used in both the Albums table as well as the Music table. This will make it easier to program against (no effect on performance, just your sanity). HTH, Sam ------------------------------------------- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -----Original Message----- From: Sreedhar.a [mailto:[EMAIL PROTECTED] Sent: Saturday, August 18, 2007 1:23 AM To: sqlite-users@sqlite.org Subject: [sqlite] To increase search speed Method 3: Joining Multiple tables. I have created 4 tables as follows; "create table ALBUMS (id integer primary key,Album text,unique(Album));" "create table ARTISTS (id integer primary key,Artist text,unique(Artist));" "create table GENRES (id integer primary key,Genre text,unique(Genre));" "create table MUSIC (Id integer primary key, AlbumName text not null collate nocase,Track text not null collate nocase,ArtistName text not null collate nocase,URL text not null collate nocase , Duration integer, TrackFormat text not null collate nocase, BitRate integer, sampleRate integer, Channels integer, Filesize integer,album_id integer,artist_id integer, GenreName text not null collate nocase ,Genre_Id integer);" Here album_id , artist_id and Genre_Id are the id values of ALBUMS,ARTISTS,GENRES Tables. This shows better performance than indexing for the following searches. Select distinct AlbumName from MUSIC; Select distinct ArtistName from MUSIC; Select distinct GenreName from MUSIC; ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
