ThisCalculator

Everyday

Clustered Covering & Partial Indexes: DB Support

Updated · Standard formula, shown below

Clustered covering and partial indexes are database performance features, but their support varies significantly across systems. This article explains which major databases support clustered indexes, covering indexes, and partial indexes, and how to use them effectively.

What Is a Clustered Index?

A clustered index determines the physical order of rows in a table. When a table has a clustered index, the database stores the actual data rows in the sorted order of the index key. This means there can be only one clustered index per table because the rows can only be physically sorted one way. In contrast, a nonclustered index is a separate structure that contains a copy of the key columns and a pointer to the actual data row.

Databases that support clustered indexes include:

  • Microsoft SQL Server – Allows one clustered index per table, typically created on the primary key by default.
  • MySQL (InnoDB) – The primary key is always the clustered index; if no primary key is defined, InnoDB uses a hidden unique column.
  • PostgreSQL – Does not have true clustered indexes, but the CLUSTER command physically reorders rows based on an index (one-time operation, not automatic).
  • Oracle – Supports index-organized tables (IOT) where the table is stored as an index; not a traditional clustered index but similar.
  • SQLite – Every table has an implicit rowid; if a column is declared INTEGER PRIMARY KEY, it becomes the clustered index.

Databases that do not support clustered indexes: MongoDB (uses a default _id index but not clustered), Cassandra (uses a different storage model).

What Is a Covering Index?

A covering index is a nonclustered index that contains all the columns needed to satisfy a query without touching the base table. For example, if a query selects only columns a and b and filters on a, an index on (a, b) covers the query. The database can read the index alone, avoiding a table lookup (key lookup or bookmark lookup).

All major relational databases support covering indexes implicitly, because you can create an index with any set of columns. However, some databases have special syntax:

  • SQL Server – Supports INCLUDE clause to add non-key columns to a covering index.
  • PostgreSQL – Supports INCLUDE since version 11.
  • MySQL – No INCLUDE clause; you simply add all needed columns to the index.
  • Oracle – No INCLUDE clause; you can use a composite index.
  • SQLite – No INCLUDE clause; composite indexes work.

What Is a Partial Index?

A partial index is an index that only includes rows that satisfy a predicate (WHERE clause). This reduces index size and maintenance overhead. For example, an index on status only for rows where status = 'active'.

Support for partial indexes:

  • PostgreSQL – Full support: CREATE INDEX idx ON table (col) WHERE condition;
  • SQLite – Full support: CREATE INDEX idx ON table (col) WHERE condition;
  • SQL Server – Supports filtered indexes: CREATE INDEX idx ON table (col) WHERE condition;
  • MySQL – Does not support partial indexes (as of MySQL 8.4).
  • Oracle – Supports partial indexes only for partitioned tables (since 12c), not for regular tables.
  • MongoDB – Supports partial indexes with partialFilterExpression.

How to Choose Between Them

Choosing the right index type depends on your query patterns and table size. Here are practical guidelines:

  • Use a clustered index for columns that are frequently used in range queries or ORDER BY, because physical order speeds up those operations.
  • Use a covering index for queries that select only a few columns and are performance-critical, to avoid table lookups.
  • Use a partial index when you frequently query a subset of rows (e.g., active users, unpaid orders) and the predicate is stable.
  • Combine covering and partial: a partial covering index can be even smaller and faster.

Common Problems and Misconceptions

One common misconception is that a covering index must contain all columns in the SELECT list. Actually, it must contain all columns that are not in the WHERE, ORDER BY, or JOIN clauses, but if you select a column that is not in the index, the database must do a lookup. Another misconception is that a clustered index always improves performance; it can slow down inserts because the database must maintain physical order.

Another issue is that partial indexes are not automatically used by the query planner; the query must have a predicate that matches the index predicate, or the planner may ignore it. Also, in SQL Server, filtered indexes require the query to use the same predicate or a subset of it.

Performance and Cost Considerations

Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the index must be updated. Clustered indexes have a bigger impact on writes than nonclustered because they affect the physical row location. Partial indexes reduce this overhead because they index fewer rows.

Storage cost is also a factor. A covering index with many columns can be larger than the table itself. Partial indexes save space. Clustered indexes do not consume extra storage beyond the table itself, but they can cause page splits when rows are inserted out of order.

Database-Specific Examples

Here are concrete examples for major databases:

  • PostgreSQL – Partial index: CREATE INDEX idx_orders_active ON orders (order_date) WHERE status = 'active'; Covering index: CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (order_total);
  • SQL Server – Filtered index: CREATE INDEX idx_orders_active ON orders (order_date) WHERE status = 'active'; Covering index: CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (order_total);
  • MySQL – Covering index: CREATE INDEX idx_orders_covering ON orders (customer_id, order_total); No partial index support.
  • Oracle – Covering index: CREATE INDEX idx_orders_covering ON orders (customer_id, order_total); Partial index only on partitioned tables: CREATE INDEX idx_orders_active ON orders (order_date) LOCAL PARTIAL ...
  • SQLite – Partial index: CREATE INDEX idx_orders_active ON orders (order_date) WHERE status = 'active';

Frequently Asked Questions

Can I have both a clustered and a covering index on the same table?

Yes. A table can have one clustered index and many nonclustered indexes. A covering index is just a nonclustered index that covers a query. For example, in SQL Server, you can have a clustered index on id and a covering index on (status, created_at) INCLUDE amount.

Does a covering index always speed up queries?

Not always. If the covering index is very wide (many columns), it may be larger than the table, causing more I/O. Also, if the query uses functions or expressions on indexed columns, the index may not be used. Always test with realistic data.

What is the difference between a partial index and a filtered index?

They are essentially the same concept. PostgreSQL and SQLite call it a partial index; SQL Server calls it a filtered index. The syntax and behavior are similar: the index only includes rows that meet a predicate.

Does MySQL support partial indexes in any way?

As of MySQL 8.4, MySQL does not support partial indexes on regular tables. You can simulate a partial index by using a generated column that is null for rows you want to exclude, and then indexing that column, but it is not the same.

Can I use a partial index with a covering index together?

Yes. You can create a partial index that includes extra columns to cover a query. For example, CREATE INDEX idx_active_covering ON orders (customer_id) INCLUDE (order_total) WHERE status = 'active'; in PostgreSQL or SQL Server.

Do NoSQL databases support these indexes?

MongoDB supports partial indexes but not clustered indexes (it has a default _id index). Cassandra uses a different storage engine and does not support clustered or covering indexes in the same way. Redis does not support these index types.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.