Re: [sqlite] Using indexed fields in a table.

[Eric Smith]

On Sat, Mar 5, 2011 at 8:14 AM, BareFeetWare wrote:

> On 05/03/2011, at 1:59 AM, "J Trahair" 
> 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
>
>
Watch this talk by an author of SQLite for a great explanation in his own
words:

http://www.youtube.com/watch?v=Z_cX3bzkExE

Eric

--
Eric A. Smith

There is no likelihood man can ever tap the power of the atom.
-- Robert Millikan, Nobel Prize in Physics, 1923
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using indexed fields in a table.

[BareFeetWare]

On 05/03/2011, at 1:59 AM, "J Trahair"  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

Re: [sqlite] Using indexed fields in a table.

[Simon Slavin]

On 4 Mar 2011, at 2:59pm, J Trahair wrote:

> You have a table called AllMusic containing columns called Composer, 
> NameOfPiece, YearComposed, etc.
> 
> SELECT * FROM AllMusic_tbl WHERE Composer = 'Bach' ORDER BY YearComposed
> 
> I thought the select would be quicker if the AllMusic table had keys or 
> indexes on a couple of important columns, eg. in this case Composer and 
> YearComposed. Plainly the indexes would not be unique - there would be 
> multiple examples of Bach and Beethoven in any Composer field and many pieces 
> composed in any single year.
> 
> Does sqlite need such indexes, and if so what is the syntax at CreateTable 
> time?

You've got the idea that indexes are important, but not got the right idea what 
indexes to create.

Forget the idea of indexing individual fields.  That's important only for some 
very specific things.  Instead of thinking "each field could have an index" 
think more like "each SELECT could have an index".  To use your fine example 
above as a model you could provide two indexes, one on Composer and the other 
on YearComposed, and they'd both be pretty useless for that SELECT.  A better 
index for that SELECT would be one index on both fields in the right order, 
which would be something like

CREATE INDEX AllMusicComposerYear ON AllMusic_tbl (Composer, YearComposed)

To convince yourself how useful this would be, imagine what you'd see if you 
wrote that index out.  First, you'd find all the Bach works listed in one 
chunk, not scattered over the entire list.  Then once you'd found the chunk of 
Bach works, within that chunk they'd already be listed in Year order.  So the 
SELECT hardly needs to do any work at all, just identify the top and bottom 
appropriate entries in the index and return everything between them in the 
order it already appears.  Indexing both fields individually wouldn't be 
anywhere as helpful as that.

So the message here is not to make extra indexes until you know what SELECTs 
you're doing. Then try to make up indexes which will allow your SELECTs to find 
their answers already prepared.  Often you can make up one index which 
satisfies more than one SELECT at one point in your code: it's ideally suited 
to one of them and may be overkill for others, doing sorting they don't need, 
but that's not a handicap.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using indexed fields in a table.

[J Trahair]

Hi

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

I thought the select would be quicker if the AllMusic table had keys or indexes 
on a couple of important columns, eg. in this case Composer and YearComposed. 
Plainly the indexes would not be unique - there would be multiple examples of 
Bach and Beethoven in any Composer field and many pieces composed in any single 
year.

Does sqlite need such indexes, and if so what is the syntax at CreateTable time?

Thanks in advance.

J Trahair
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users