[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Question about index usage
From:       "Keith Medcalf" <kmedcalf () dessus ! com>
Date:       2013-08-31 19:42:04
Message-ID: 09c4179efe518c40bed8b34d8f21110f () mail ! dessus ! com
[Download RAW message or body]


> > 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[prev in list] [next in list] [prev in thread] [next in thread]