On Fri, Mar 14, 2008 at 12:42:49PM -0600, Lee Crain scratched on the wall: > I am debating the performance gains to be realized, if any, by indexing a > particular table in a system implementation for which I am responsible. > > __________ > > This is the table creation command: > > CREATE TABLE CC ( > [catIndex] [integer] NOT NULL, > [contIndex] [integer] NOT NULL, > [new] [integer] NOT NULL DEFAULT(0)); > > Pretty simple: a table containing 3 fields, all integers. > > __________ > > If I were to index this table, I would implement this index creation > command: > > CREATE UNIQUE INDEX CC_CatContNewIndex on CC ([catIndex] ASC, [contIndex] > ASC, [new] ASC); > > Pretty simple: an index containing 3 fields, all integers, sorted > ascending order.
Others have answered your direction question: indexes will slow writes (and more indexes will slow writes more), but should never slow searches. I wanted to add a few other comments, as the style of this index is somewhat unique. This index assumes a pretty specific access pattern. In order to fully utilize this index for search purposes, you'd need to provide search values for all three columns in your WHERE clause. Since you already have all three values, this is only useful if you need to ask the question "does this record exist?". The index is much less suited for using one column value to find another unless you search in a very specific order. The index won't be used at all unless your WHERE clause provides a search value for [catIndex]. If you're looking for a row based off [contIndex] or [new] (or both), the index can't be used. Similarly, if you search based off [catIndex] and [new], the index will only be used to find values in [catIndex]. If there are multiple rows with the same [catIndex] value (which is allowed by this index since the UNIQUE constraint applies across all three columns as a set), those rows will be searched linearly for a row(s) with the correct [new] value. If, on the other hand, you commonly search on [catIndex] looking for a [contIndex] value, or look for a [contIndex] value looking for a [catIndex], it might make more sense to create a different index for each individual column. Also, if your intent was to enforce a UNIQUE constraint on each individual column, you have to create a specific UNIQUE INDEX on each column. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
