Lee, If you always access by all 3 fields then yes. Index on all 3.. But If say you mainly access by only two fields say: catIndex, contIndex
Then just index on those two. That should save a little space but the trade off is that when you need the third fields sqlite will need to do 1 additional read, to get that data. So in the end its really your call, Space vs time. Regards, Ken Lee Crain <[EMAIL PROTECTED]> wrote: Understood. I will be writing our software to search this table by values, so it sounds like indexing to improve read access is desirable. The reason I was wondering about indexing is that the indexes contain exactly the same data as is in the table and this seemed like an unnecessary duplication of data. However, if your remarks are accurate, our application would still see read access efficiencies by indexing the data fields. Lee P.S. I've sent my original email to the SQLite forum several times because our mail server is showing that it was rejected. Obviously, this is not true. I apologize for the extra sends. _____________________ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David McDivitt Sent: Friday, March 14, 2008 1:07 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Efficiency Question - Value Or Liability for Indexingof This Table? This depends on the way you plan to access the table. Indexing reduces the effect of table scans, or where clauses. If you will not be fetching records by finding a value in a given field, do not add that field to any supplemental indexes. Indexes slow the database down when writing, since each index must be maintained. **************** 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. __________ The question: Could improvements in search and access performance be realized by indexing this table? Or, might having an index actually decrease table write and/or read performance? Thanks, Lee _______________________________________________ _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
