Index Types | DLP Data Solutions

Understanding Indexes in SQL Server, MySQL, and PostgreSQL

Indexes help databases locate rows efficiently. In this section, we’ll explore nine common index categories and show how to create them in SQL Server, MySQL, and PostgreSQL.

These categories overlap: an index can be both composite and unique, for example. Some describe the columns indexed, while others describe storage, filtering, or specialized search capabilities.

The SQL Server examples primarily use AdventureWorks2022. The MySQL and PostgreSQL examples assume equivalent tables exist, with names adjusted for each platform. Treat the examples as separate demonstrations; AdventureWorks already includes several similar indexes.

Case and accent sensitivity depend on the applicable collation, rather than the index category.

1. Single-Column Indexes

A single-column index is the simplest starting point. It indexes one column and can help queries that filter, join, or sort on that column. Numeric identifiers are common candidates, but other supported data types—such as dates and strings—can also be indexed.

SQL Server

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID
ON Sales.SalesOrderHeader (CustomerID);

MySQL and PostgreSQL

CREATE INDEX ix_salesorderheader_customerid
ON salesorderheader (customerid);

For these examples, MySQL and PostgreSQL omit SQL Server’s NONCLUSTERED keyword. See the SQL Server, MySQL, and PostgreSQL syntax references.

2. Composite Indexes

A composite index contains two or more key columns. Column order matters.

The following index organizes its keys by CustomerID, then by OrderDate within each customer.

SQL Server

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_OrderDate
ON Sales.SalesOrderHeader (CustomerID, OrderDate);

MySQL and PostgreSQL

CREATE INDEX ix_salesorderheader_customerid_orderdate
ON salesorderheader (customerid, orderdate);

This index is generally well suited to queries that filter by CustomerID, or by both CustomerID and OrderDate.

A query that filters only by OrderDate is usually less well served by this column order. However, calling the index “useless” would be too strong: the optimizer may still use it, depending on the database, data distribution, and query. PostgreSQL multicolumn index documentation

3. Unique Indexes

A unique index prevents duplicate key values. When it contains multiple columns, the combination of those values must be unique.

SQL Server

CREATE UNIQUE NONCLUSTERED INDEX UX_Employee_NationalIDNumber
ON HumanResources.Employee (NationalIDNumber);

MySQL and PostgreSQL

CREATE UNIQUE INDEX ux_users_email
ON users (email);

A table can have multiple unique indexes, although it can have only one primary key. Unique indexes are useful for additional identifiers, such as email addresses, employee numbers, or combinations of business attributes. Treatment of NULL values differs among platforms. SQL Server index documentation, PostgreSQL index documentation

4. Clustered Indexes

Clustered storage organizes table data around an index key. Its implementation differs substantially among SQL Server, MySQL, and PostgreSQL.

SQL Server

A SQL Server rowstore table can have one clustered index. Its leaf level contains the table’s data rows. The clustered key does not have to be the primary key. SQL Server clustered and nonclustered indexes

The syntax is:

CREATE CLUSTERED INDEX CX_Employee_BusinessEntityID
ON HumanResources.Employee (BusinessEntityID);

AdventureWorks2022 already has a clustered index on this table, so this statement illustrates the syntax rather than an additional index to create.

PostgreSQL

PostgreSQL can physically reorder a table using an existing index:

CREATE INDEX ix_orders_order_date
ON orders (order_date);

CLUSTER orders USING ix_orders_order_date;
ANALYZE orders;

CLUSTER performs a one-time reordering. PostgreSQL does not automatically maintain that physical order as rows change. PostgreSQL CLUSTER documentation

MySQL

With MySQL’s InnoDB storage engine, the primary key serves as the clustered index:

CREATE TABLE orders (
    order_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    PRIMARY KEY (order_id)
) ENGINE = InnoDB;

If no primary key exists, InnoDB uses the first suitable unique index whose columns are all NOT NULL. If neither exists, it creates a hidden clustered index. Defining an explicit primary key gives you control over this choice. InnoDB clustered and secondary indexes

5. Nonclustered and Secondary Indexes

A nonclustered index stores index keys separately from the table’s data, along with information needed to locate the corresponding rows.

SQL Server

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID
ON Sales.SalesOrderHeader (CustomerID);

This is the same index introduced in the first example: it is both single-column and nonclustered.

A heap is a SQL Server table without a clustered index. It is not another name for a nonclustered index. A heap can have nonclustered indexes. SQL Server index organization

MySQL and PostgreSQL

CREATE INDEX ix_salesorderheader_customerid
ON salesorderheader (customerid);

In InnoDB, an index other than the clustered index is called a secondary index. Its entries contain the clustered key used to locate the row. MySQL secondary indexes

PostgreSQL indexes are separate from table storage and do not use SQL Server’s NONCLUSTERED keyword. PostgreSQL CREATE INDEX

6. Filtered and Partial Indexes

Sometimes only a subset of rows needs an index. SQL Server calls this a filtered index; PostgreSQL calls it a partial index.

For example, an order-processing application might frequently retrieve orders that have not shipped.

SQL Server

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_Unshipped
ON Sales.SalesOrderHeader (OrderDate, CustomerID)
INCLUDE (SalesOrderID, ShipDate)
WHERE ShipDate IS NULL;

PostgreSQL

CREATE INDEX ix_salesorderheader_unshipped
ON salesorderheader (orderdate, customerid)
INCLUDE (salesorderid, shipdate)
WHERE shipdate IS NULL;

These indexes contain only rows where ShipDate is NULL. The INCLUDE columns provide additional values without making them part of the search key. This can help eligible queries retrieve everything they need from the index. SQL Server index syntax, PostgreSQL partial indexes

MySQL

MySQL does not directly support a WHERE clause on CREATE INDEX. An indexed generated column can help with certain conditional searches, but it does not create a true partial index that omits nonmatching rows. MySQL index syntax

7. Full-Text Indexes

Full-text indexes support searches for words and phrases inside text columns. They process text into searchable tokens, with behavior that depends on language settings, stopwords, and the database’s search implementation.

SQL Server

First, check whether Full-Text Search is installed:

SELECT
    @@VERSION AS SQLServerVersion,
    SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled;

A result of 1 indicates that the component is installed. A result of 0 means it is not installed. SQL Server SERVERPROPERTY

With Full-Text Search available, create a catalog and index:

CREATE FULLTEXT CATALOG AdventureWorksFullText AS DEFAULT;
GO

CREATE FULLTEXT INDEX ON Production.ProductDescription (
    Description LANGUAGE 1033
)
KEY INDEX PK_ProductDescription_ProductDescriptionID;

1033 specifies English. KEY INDEX identifies an existing unique, single-column, non-nullable index that SQL Server uses to identify rows. SQL Server full-text index documentation

Search the indexed descriptions with CONTAINS:

SELECT
    ProductDescriptionID,
    Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, '"aluminum" OR "lightweight"');

PostgreSQL

PostgreSQL supports full-text search with tsvector values and commonly uses a GIN index:

CREATE INDEX ix_productdescription_fulltext
ON production.productdescription
USING GIN (to_tsvector('english', coalesce(description, '')));

A matching query uses the same indexed expression:

SELECT
    productdescriptionid,
    description
FROM production.productdescription
WHERE to_tsvector('english', coalesce(description, ''))
      @@ to_tsquery('english', 'aluminum | lightweight');

The | operator means OR. Specifying the text-search configuration explicitly keeps indexing and querying consistent. PostgreSQL full-text indexing

MySQL

CREATE FULLTEXT INDEX ft_productdescription_description
ON ProductDescription (Description);

A natural-language search can return results ranked by relevance:

SELECT
    ProductDescriptionID,
    Description,
    MATCH(Description) AGAINST(
        'aluminum lightweight' IN NATURAL LANGUAGE MODE
    ) AS relevance
FROM ProductDescription
WHERE MATCH(Description) AGAINST(
    'aluminum lightweight' IN NATURAL LANGUAGE MODE
)
ORDER BY relevance DESC;

Natural-language mode looks for relevant matches; it does not require every search term to appear in each result. MySQL natural-language full-text search

8. Spatial Indexes

Spatial indexes help queries work efficiently with locations, lines, and regions. Common tasks include finding nearby addresses, identifying intersecting areas, and checking whether a point lies within a boundary.

Spatial functions perform the calculations. A spatial index helps eligible queries narrow the objects they need to examine.

SQL Server

AdventureWorks2022 stores address locations in the SpatialLocation column using the geography data type.

CREATE SPATIAL INDEX IX_Address_SpatialLocation
ON Person.Address (SpatialLocation)
USING GEOGRAPHY_AUTO_GRID
WITH (CELLS_PER_OBJECT = 12);

GEOGRAPHY_AUTO_GRID lets SQL Server manage the spatial grid configuration. Creating a spatial index requires a clustered primary key, which the AdventureWorks Person.Address table already has. SQL Server spatial index documentation

The following query returns up to five addresses within 10 kilometers of a point in Seattle, ordered from nearest to farthest. It converts meters to miles for display.

DECLARE @SearchLocation geography =
    geography::Point(47.6062, -122.3321, 4326);

SELECT TOP (5)
    AddressID,
    AddressLine1,
    City,
    SpatialLocation.STDistance(@SearchLocation) / 1609.344 AS DistanceInMiles
FROM Person.Address
WHERE SpatialLocation IS NOT NULL
  AND SpatialLocation.STDistance(@SearchLocation) <= 10000
ORDER BY DistanceInMiles ASC;

The returned addresses depend on the coordinates stored in your database.

MySQL

Create a spatial column with an explicit spatial reference system identifier, or SRID:

CREATE TABLE geom (
    g GEOMETRY NOT NULL SRID 4326
);

CREATE SPATIAL INDEX ix_geom_g
ON geom (g);

The column must be NOT NULL. An explicit SRID restriction allows the optimizer to consider the spatial index and ensures that column values use the specified spatial reference system. Assigning an SRID identifies the coordinate system; it does not transform coordinates from another system. MySQL spatial index optimization

PostgreSQL with PostGIS

For a PostGIS geometry column, use GiST:

CREATE INDEX idx_places_geom
ON places USING GIST (geom);

PostGIS implements an R-tree spatial index through GiST. Omitting USING GIST creates a standard B-tree index by default, which does not provide the intended spatial search acceleration. The query must also use an index-aware spatial operation for the index to help. PostGIS spatial index documentation

9. Functional and Expression Indexes

Functional or expression indexes store the results of expressions, such as a lowercase email address. SQL Server commonly handles this through an indexed computed column.

SQL Server

This example adds a computed column containing the order year, then indexes that column:

ALTER TABLE Sales.SalesOrderHeader
ADD OrderYear AS YEAR(OrderDate) PERSISTED;

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_OrderYear
ON Sales.SalesOrderHeader (OrderYear);

PERSISTED stores the computed value in the table and keeps it updated when its source values change. The index is a separate structure created by the second statement. Persistence is not required for every indexable computed column; SQL Server also applies determinism, precision, and connection-setting requirements. SQL Server indexes on computed columns

A query can reference the computed column directly:

SELECT
    SalesOrderID,
    OrderDate,
    CustomerID
FROM Sales.SalesOrderHeader
WHERE OrderYear = 2013;

MySQL

MySQL 8.0.13 and later support functional index key parts:

CREATE INDEX idx_users_email_lower
ON users ((LOWER(email)));

The extra parentheses around the expression are required. MySQL functional index syntax

PostgreSQL

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

A query matching the indexed expression can use this index:

SELECT email
FROM users
WHERE LOWER(email) = 'alex@example.com';

Expression indexes are useful when queries repeatedly search on the same calculated value. Their benefit depends on the query and existing comparison behavior—for example, whether an email column already uses a case-insensitive collation. PostgreSQL expression index syntax