Should i index a colum part of primary key

Question

I'm well aware that an index is automatically created with a primary key. Nevertheless what about multi column primary key ?

Lets say i have this table :

CREATE TABLE mytable(
    created_at TEXT NOT NULL, 
    uuid TEXT NOT NULL, 
    val INTEGER NOT NULL, 
    PRIMARY KEY(created_at, uuid));

Querying like this

SELECT * FROM mytable WHERE created_at > '2018-01-01' AND uuid = 'abc'

will use the primary key index.

But what if i only search on uuid for instance?

SELECT * FROM mytable WHERE uuid = 'abc'

Should i recreate an index on the uuid column alone or the primary index will just works fine ?

CL. CL. · Accepted Answer · 2018-01-22T17:55:38

sqlite> SELECT * FROM mytable WHERE created_at > '2018-01-01' AND uuid = 'abc';
--EQP-- 0,0,0,SEARCH TABLE mytable USING INDEX sqlite_autoindex_mytable_1 (created_at>?)
sqlite> SELECT * FROM mytable WHERE uuid = 'abc';
--EQP-- 0,0,0,SCAN TABLE mytable

However, in some circumstances, the PK index might be used:

SQLite might use a skip-scan on an index if it knows that the first one or more columns contain many duplication values. […]
The only way that SQLite can know that the left-most columns of an index have many duplicate is if the ANALYZE command has been run on the database.

Depending on the queries you run, it might be a better idea to make uuid the first column of the primary key constraint.

Should i index a colum part of primary key

1 Answers