CODEWITHSUNDEEP
sqlite
Nyaruko
Nyaruko

Reputation: 4459

sqlite: Do I need a index if I already have multi-column primary key?

If I create a table like this:

CREATE TABLE IF NOT EXISTS Tiles 
(
     X INTEGER NOT NULL, 
     Y INTEGER NOT NULL, 
     Zoom INTEGER NOT NULL, 
     Type INTEGER NOT NULL, 
     Tile BLOB NULL,
     PRIMARY KEY(X, Y,Zoom,Type)
)

Do I still need to add a index on (X, Y, Zoom, Type) to make the searching fast?

My search query is like this:

SELECT Tile 
FROM Tiles 
WHERE X = %1 AND Y = %2 AND Zoom = %3 AND Type = %4

My insert query is like this:

REPLACE INTO Tiles(X, Y, Zoom, Type, Tile) 
VALUES(?, ?, ?, ?, ?)

Upvotes: 0

Views: 29

Answers (1)

Renzo
Renzo

Reputation: 27414

In SQLite, when you declare a primary key an index is automatically created, see for instance the documentation, so there is no need to create another index.

Upvotes: 1

Related Questions