[sqlite] Using indexed fields in a table.
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
Re: [sqlite] Using indexed fields in a table.
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
Re: [sqlite] Using indexed fields in a table.
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.
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