On 05/03/2011, at 1:59 AM, "J Trahair" <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
