Indexes

An index is a lookup structure that lets the database find matching rows without scanning the whole table. Looking up a book by its author is a common query in the example library, so an index on books.author_id is worthwhile:

add index as idx_books_author on books (author_id)

show indexes lists every index in the project, qualified by its table:

show indexes

Indexes (1):
  books.idx_books_author (author_id)

Seeing the difference an index makes

Prefix a query with explain to see how the database would run it, without running it (see Query plans). With the index in place, a lookup on author_id uses it:

explain show data books where author_id = 1

SELECT "book_id", "title", "author_id", "published", "isbn" FROM "books" WHERE "author_id" = 1
└─ SEARCH books USING INDEX idx_books_author (author_id=?)

A filter on a column with no index has to read every row instead — a scan:

explain show data books where published > 1968

SELECT "book_id", "title", "author_id", "published", "isbn" FROM "books" WHERE "published" > 1968
└─ SCAN books

SEARCH … USING INDEX is the fast path; SCAN reads the whole table. On a few rows the difference is invisible, but it is the core idea behind why indexes exist — and the plan tree lets you see which one your query gets.

Multi-column indexes

An index can cover more than one column; list them in order. This helps queries that filter or sort on that same leading combination:

add index as idx_loans_book_member on loans (book_id, member_id)

Dropping an index

Drop it by name, or by the columns it covers:

drop index idx_books_author
drop index on books (author_id)

In advanced mode

create index idx_books_author on books (author_id)
create unique index idx_books_isbn on books (isbn)
drop index idx_books_author

A unique index doubles as a uniqueness constraint — it both speeds up lookups and rejects duplicate values. (In simple mode, uniqueness is a constraint on the column rather than an index option.)

Syntax

add index [as <Name>] on <Table> (<col>[, ...])
drop index <Name>
drop index on <Table> (<col>[, ...])

See also Constraints and Querying & inspecting.