CODEWITHSUNDEEP
indexingsqlite
grunk
grunk

Reputation: 14938

Should i index a colum part of primary key

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 ?

Upvotes: 1

Views: 399

Answers (1)

CL.
CL.

Reputation: 180020

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.

Upvotes: 1

Related Questions