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
