On Wed, Feb 23, 2011 at 03:10:02PM +0100, Sven L scratched on the wall: > > Does this trick work on the primary key? If not, why?
Yes, all the time. Defining a column as a PK automatically creates a UNIQUE index over that column. The only exception is when the column is an INTEGER PRIMARY KEY, in which case the column becomes the rowid and uses the table's native index. In that case, to achieve the same result, one would need to manually create an index, as Max outlines below. In addition to fast counts, this setup is also very good for equi-joins, which tend to be somewhat common in most database designs. (An equi-join is when you join table A to table B only for the purpose of filtering rows in A, and don't actually return any values from B as part of the result set.) Because of the way SQLite works internally, there are also tricks of creating indexes with "extra" columns. For example, if you have a table with 23 columns, but you mostly use the PK and two additional columns, you can create an index over just those tree columns. This will generally result in faster access to those two columns (for reasons that take several pages to explain). Of course, even better would be to break things up into a primary and detail table, but that's a design consideration. Just beware that all of these indexes come at a cost. If you database is strongly read dominated, you might consider some of these techniques. On the other hand, if you're more or less read/write mixed, or write dominated, these techniques will cause an overall performance drop. Indexes can be useful for reads, but they always come at a write (INSERT/UPDATE/DELETE) cost. -j > > Returning to the original topic, for performance reasons I sometimes > > recommend using an index created on the id/rowid. It's a strange construct > > that makes no sense, but actually it sometimes give a speed improvement. > > > > This is because any index contains only the data used in it and if the query > > doesn't require getting additional data from the table it was created for, > > sqlite only reads this index and nothing else. -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
