Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Transactions and Performance

SQL index types

A guide to SQL index types: the default B-tree, composite and column order, partial and expression indexes, covering indexes, hash, GIN and clustered indexes.

An index is a separate, sorted structure that lets the database find rows without reading the whole table, like the index at the back of a book. Different index types suit different questions. Most of the time a plain B-tree is the right answer; the other types handle special cases.

Every index speeds up some reads and slows down writes, because inserts, updates and deletes must maintain it too. Create indexes for queries you really run, and check with EXPLAIN QUERY PLAN that they are used.

B-tree indexes (the default)

CREATE INDEX builds a B-tree in every database. It keeps keys in sorted order, which supports equality (=), ranges (<, BETWEEN), prefix searches (LIKE 'abc%' in suitable collations), ORDER BY without a sort step, and MIN/MAX.

Composite indexes and column order

An index on (a, b) is sorted by a, then by b within each a. It helps queries that filter on a, or on a and b, but not queries that filter on b alone. Put equality columns first and the range or sort column last.

SQL Composite index: leading column matters ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first plan reads SEARCH orders USING INDEX idx_orders_customer_status (customer_id=? AND status=?), a direct lookup on both columns. The second plan is a SCAN of the whole table, because status is not the leading column and the index cannot be searched by it.

Partial indexes

A partial index covers only rows that match a WHERE condition. It is smaller, cheaper to maintain, and perfect when you query a small slice, such as open orders or non-deleted rows. Supported by SQLite, PostgreSQL and SQL Server (filtered indexes); Oracle gets a similar effect with function-based indexes, and MySQL has no partial indexes.

Expression indexes

An index on an expression, such as lower(email), makes queries on that exact expression fast. The query must use the same expression.

Covering indexes

A covering index contains every column a query needs, so the database answers from the index alone and never touches the table. In SQLite and MySQL you append the extra columns to the key. PostgreSQL and SQL Server offer INCLUDE (col), which stores them in the index without making them part of the sort key.

SQL Partial and covering indexes ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both plans say COVERING INDEX: the table is never read. The first one scans only the small partial index and needs no sort step, because the index is already ordered by ordered_at; the second searches by order_id.

Other index types

Type Where Good for
Unique everywhere Enforcing uniqueness (CREATE UNIQUE INDEX); the basis of UNIQUE and PRIMARY KEY.
Hash PostgreSQL, MySQL (MEMORY tables) Equality lookups only; no ranges or sorting.
GIN PostgreSQL Containment in arrays, JSONB, full-text search.
GiST / SP-GiST PostgreSQL Geometry, ranges, nearest-neighbor searches.
BRIN PostgreSQL Huge tables whose values correlate with physical order, such as timestamps.
Full-text MySQL FULLTEXT, SQL Server, SQLite FTS5 (virtual table) Word searches.
Spatial MySQL, PostgreSQL (PostGIS), SQL Server, Oracle Location queries.
Bitmap Oracle Low-cardinality columns in warehouses.
Clustered SQL Server, MySQL InnoDB (the primary key); SQLite WITHOUT ROWID tables Stores the table rows in index order, so there is only one per table.
SQL
-- PostgreSQL
CREATE INDEX idx_docs_data ON docs USING GIN (data);
CREATE INDEX idx_events_time ON events USING BRIN (created_at);

-- SQL Server
CREATE NONCLUSTERED INDEX idx_orders_customer ON orders (customer_id) INCLUDE (ordered_at);

Usage notes

  • Index foreign key columns you join or delete through; many databases do not create them automatically.
  • Do not index everything. Each extra index costs disk space and slows writes.
  • Low-selectivity columns (booleans, a few statuses) are poor index candidates unless the index is partial.
  • A function on the column defeats the index unless an expression index matches; see sargable predicates.
  • Rebuild and statistics: after big data changes run ANALYZE (see VACUUM and ANALYZE).
  • Verify with the query plan rather than guessing.