Re: [sqlite] Do I still need an index if I have a composite Primary Key(or UNIQUE)

2011-09-24 Thread Igor Tandetnik 
Florian Kusche  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

[sqlite] Do I still need an index if I have a composite Primary Key (or UNIQUE)

2011-09-24 Thread Florian Kusche 
Hi,

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?

Is this dependant on the order of the columns in the primary key? i.e.: if no 
additional index is needed, would it still work, if the primary key was 
"PRIMARY KEY ( Order, IconID )" ?

Thanks for your help!

Florian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users