These index terms describe different things: clustered concerns how a table’s rows are organized, covering means an index contains the values a particular query needs, and partial means an index represents only a subset of data. SQL Server, MySQL with InnoDB, PostgreSQL, SQLite, and Oracle Database implement these ideas differently; the labels are not interchangeable SQL features.
How the five database engines compare
| Database / engine | Clustered behavior | Covering behavior | Partial or subset behavior |
|---|---|---|---|
| SQL Server | One clustered index organizes the table’s rows; a table without one is a heap. | A nonclustered index can contain key and included columns needed by a query. | Filtered indexes cover a defined row subset. Check predicate and uniqueness requirements for the deployed release. |
| MySQL with InnoDB | Table rows are stored in the clustered index, generally organized by the primary key. Without a declared primary key, InnoDB uses a suitable non-null unique key or creates an internal clustered key. | A query may be served from index records when they contain all values it needs. | The MySQL 8.0 manual reviewed for this comparison does not document a general row-predicate CREATE INDEX ... WHERE feature. |
| PostgreSQL | Tables use heap storage separate from indexes. CLUSTER rewrites a table according to an index’s order; later writes do not maintain that order automatically. |
An index-only scan may return data from the index when it contains the required columns and visibility conditions permit. | A partial index stores entries only for rows satisfying its predicate. |
| SQLite | SQLite’s feature overview lists clustered indexes, but that wording is not a complete operational definition of a separately declared clustered index and should not be equated with SQL Server’s storage model. | A covering index can provide the values a query needs without a table lookup. | A partial index uses a row predicate in CREATE INDEX. SQLite documents availability from version 3.8.0; older releases cannot read or write schemas containing partial indexes. |
| Oracle Database | An index-organized table (IOT) stores table data in a primary-key B-tree; this differs from SQL Server clustered-index syntax and need not have identical constraints. | Index scans can return requested data from an index when it contains the query’s required columns; the selected plan depends on the statement and optimizer. | Documented partial indexes for partitioned tables include or exclude table partitions according to their indexing property, rather than filtering arbitrary rows with a predicate. |
This comparison covers these five products, not every database engine or compatible product. Product claims and syntax are version-specific; check the exact release in use.
As an Amazon Associate I earn from qualifying purchases.
What “clustered” means in practice
Clustered describes a storage relationship, not one universal index declaration. In SQL Server and InnoDB, the clustered index is the table’s row storage. SQL Server permits one clustered organization for a table; if there is no clustered index, the table is a heap. InnoDB normally organizes that storage around the primary key, and its secondary index entries carry the primary-key value used to locate a row.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Oracle’s closest comparison is an IOT, whose data is held in a primary-key B-tree. PostgreSQL is materially different: its indexes remain separate from heap storage. Its CLUSTER operation arranges a table using an index at the time it runs, rather than continuously keeping the table in that order as rows change. For SQLite, do not infer a SQL Server-style clustered-table model from the feature overview alone; the exact table organization and planner behavior need to be evaluated for the SQLite version and schema in question.
#1 Best Overall
When an index is covering
“Covering” is a relationship between an index and a query, not a permanent property that makes an index useful to every query. The index must contain the columns needed to evaluate the relevant conditions and produce the requested values. A different query may need columns the index does not contain.
SQL Server nonclustered indexes can keep nonkey columns as included columns, separate from the key columns. PostgreSQL describes the corresponding access path as an index-only scan; Oracle documents index scans that can return data from the index, and MySQL uses the covering-index concept. In each case, having the necessary values in an index makes an index-based plan possible, but does not guarantee the optimizer will choose it. Cost estimates, statistics, storage or visibility details, and the specific statement affect that decision.
Adding columns solely to cover more queries has a trade-off: wider indexes consume more storage and add work when affected data changes. Judge the selected plan and the write workload alongside the read query; “covering” alone is not evidence of a performance improvement.
What counts as a partial index
For PostgreSQL and SQLite, a partial index contains entries only for rows matching a row predicate. SQL Server’s filtered index is the comparable row-subset feature, although its name and release-specific restrictions differ. A query can benefit only when its conditions are compatible with the indexed subset and the optimizer can use that index.
Rank #3
PostgreSQL imposes definition constraints: predicate functions and operators must be immutable, and predicates refer to the indexed table rather than using subqueries or aggregates. For SQL Server, verify the actual predicate and unique-index rules for the release you deploy. Oracle’s documented partial-index behavior is different: it selects table partitions according to their indexing property, and those partial indexes cannot enforce unique constraints.
For MySQL, the bounded conclusion is that the reviewed MySQL 8.0 manual does not document a general row-predicate index clause. That does not establish what every MySQL-compatible product or other release supports. Functional indexes or engine-specific techniques should not be treated as equivalent to row-predicate partial indexes without checking the target product’s documentation.
Quick Recap
Rank #4
- Used Book in Good Condition
How to choose among them
- Consider clustered storage when the engine offers it and the table’s storage organization fits the workload. Compare how secondary indexes find rows and whether subsequent writes preserve the organization.
- Consider a covering index when a specific query repeatedly needs a limited set of columns. Confirm that the chosen execution plan uses the index and weigh the read benefit against index size and update costs.
- Consider a partial or filtered index when a useful, stable subset of rows can be expressed in the engine’s supported form. Confirm the query conditions match that subset and check uniqueness, partitioning, and predicate restrictions.
- Verify the deployed engine and version before adopting syntax or assuming comparable behavior. The name “partial,” in particular, can mean a row predicate in one engine and a selected set of table partitions in another.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
