Does SQLite multi column primary key need an additional index?

Question

If I create a table like so:

CREATE TABLE something (column1, column2, PRIMARY KEY (column1, column2));

Neither column1 nor column2 are unique by themselves. However, I will do most of my queries on column1.

Does the multi column primary key create an index for both columns separately? I would think that if you specify a multi column primary key it would index them together, but I really don't know.

Would there be any performance benefit to adding a UNIQUE INDEX on column1?

Answer 1

There will probably not be a performance benefit, because queries against col1=xxx and col2=yyy would use the same index as queries like col1=zzz with no mention of col2. But my experience is only Oracle, SQL Server, Ingres, and MySQL. I don't know for sure.

Answer 2

You certainly don't want to add a unique index on column 1 as you just stated:

Neither column1 nor column2 are unique by themselves.

If column one comes first, it will be first in the multicolumn index in most databases and thus it is likely to be used. The second column is the one that might not use the index. I wouldn't add one on the second column unless you see problems and again, I would add an index not a unique index based on the comment you wrote above.

But SQL lite must have some way of seeing what it is using like most other databases, right? Set the Pk and see if queries uing just column1 are using it.