> > CREATE TABLE DevProps > > ( > > CompID INTEGER NOT NULL, > > PropID INTEGER NOT NULL > > ); > > > > CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID, > > PropID); > ... > > SELECT CompID FROM DevProps WHERE PropID=33 > > > > it looks like it will be doing a table scan. Why is that? > > Because the first column of your index is not PropID. Your index is > ordered by CompIDl. > > PropID 33 might belong to any CompID. SQLite has to look at every pair > to find them all. It might as well scan the table. > > I would recomend adding ", primary key (CompID, PropID)" to the table > for semantic reasons, and creating an index with just PropID for > performance if you want to avoid the table scan.
I would add a "primary key (CompID, PropID)" and "unique (PropID, CompID)" -- that is, unique indexes over both columns in both directions. There is little point on indexing over CompID only as this will require that all queries perform additional accesses against the base table to retrieve each CompID. Unless of course your tables are trivially small and will not "get big". Definition of "trivially small" and "get big" are in the eyes of the beholder (for example, for some people "trivially small" means a couple thousand rows, and "really big" means several million rows. Of course, depending on your other access paths, you may end up changing removing the unique index from the table definition and making it a regular additional index with additional columns in order to optimize other queries. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
