On 05/03/2011, at 1:59 AM, "J Trahair" <j.trah...@foreversoftware.co.uk> wrote:
> I understand about Primary keys and Unique keys attributed to their own > field. Is there a need to have other fields indexed, for faster searching? > Eg. a table containing your favourite music. Say you have 9 Beethoven > symphonies (one row in the table for each), 14 Bach partitas and 100 Haydn > symphonies, and you want to select the Bach ones. > You have a table called AllMusic containing columns called Composer, > NameOfPiece, YearComposed, etc. > > SELECT * FROM AllMusic_tbl WHERE Composer = 'Bach' ORDER BY YearComposed In addition to Simon's fine answer, you should also consider properly normalizing your database. In essence, this means removing redundancy and replacing any repeated values with references to discrete rows in their own table. This also makes indexes automatic, since selects use the primary key (which is automatically indexed) of each table. In short, you use less space, get faster queries, consistent data and can more efficiently changing of data (ie one entry instead of many). Something like this: begin immediate ; create table Music ( ID integer primary key not null , Name text unique not null collate nocase , Composer integer references Composer (ID) , "Year Composed" integer ) ; create table Composer ( ID integer primary key not null , Name text unique not null collate nocase ) ; commit ; You don't need any extra indexes. Your select now looks like: select Music.Name , "Year Composed" from Music join Composer on Music.Composer = Composer.ID where Composer.Name = 'Bach' order by "Year Composed" ; Tom BareFeetWare _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users