Florian Kusche <hj2...@k1k.eu> wrote: > in my application, an Icon is made up of one or more layers of images: > > CREATE TABLE IconLayer > ( > IconID INTEGER NOT NULL, > Order INTEGER NOT NULL, > ImageID INTEGER NOT NULL REFERENCES Image( _id ), > > PRIMARY KEY ( IconID, Order ) > ); > > During runtime, I want to get the layers of my Icons: > > SELECT ImageID FROM IconLayer WHERE IconID=xyz ORDER BY Order > > Do I still need an index on the column IconID, or is the primary key > sufficient, because it already contains the IconID?
Primary key is sufficient. For the future, run your query with the words "EXPLAIN QUERY PLAN" prepended in front - this will tell you exactly which indexes are used for which tables when executing the query. > Is this dependant on the order of the columns in the primary key? Yes. The order you have them in is will suited for this query. > i.e.: if no additional index is needed, would it still work, if > the primary key was "PRIMARY KEY ( Order, IconID )" ? This index could still be used, but only to satisfy ORDER BY clause. A condition on IconID would have required a full table scan. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
