SQL guide · Tables, views & databases (DDL)

SQL DROP INDEX: Remove an Index From a Table

DROP INDEX deletes an index from the database. The table and its rows are not touched; only the lookup structure is removed, along with the disk space it used. Queries that relied on the index still return the same results, but they may run more slowly because the database falls back to scanning the table.

People drop indexes for a few reasons. An index may no longer match how the application queries the data, it may duplicate another index that already covers the same columns, or it may slow down writes on a busy table without helping any reads. Before dropping, check usage statistics where your database provides them, because an index that looks unused may serve a monthly report.

The syntax is the part that differs most between databases. Some require the table name, some do not, and some only accept the index name qualified by schema. A unique index that enforces a UNIQUE or PRIMARY KEY constraint cannot be dropped directly in most systems; you remove the constraint with ALTER TABLE and the index goes with it.

Basic syntax

-- PostgreSQL, Oracle, SQLite
DROP INDEX [IF EXISTS] index_name;

-- MySQL
DROP INDEX index_name ON table_name;

-- SQL Server
DROP INDEX index_name ON table_name;

index_name is the name given when the index was created. IF EXISTS stops the statement from failing when the index is absent, and is available in PostgreSQL, SQLite, SQL Server 2016 and later, and recent MariaDB. Old SQL Server code used the form DROP INDEX table_name.index_name, which still works but is deprecated.

SQL DROP INDEX examples

Drop an index in PostgreSQL or SQLite

These engines identify an index by its name alone, because index names are unique within the schema or database. Adding IF EXISTS makes the script safe to run again after the index is gone.

DROP INDEX IF EXISTS idx_orders_customer_id;

Drop an index in MySQL or SQL Server

Here the table is part of the statement, since index names only need to be unique per table. The table keeps all its data and other indexes.

DROP INDEX idx_orders_customer_id ON orders;

Drop an index without blocking queries (PostgreSQL)

A regular DROP INDEX takes a lock that blocks queries on the table while it runs. CONCURRENTLY avoids that on a live system, with the restriction that it cannot be run inside a transaction block and can only drop one index at a time.

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_created_at;

Remove an index that backs a constraint

An index created by a UNIQUE constraint is removed by dropping the constraint, not the index. The index disappears together with the rule it enforced, so do this only when you are sure uniqueness is no longer required.

ALTER TABLE customers
DROP CONSTRAINT uq_customers_email;

-- MySQL equivalent
ALTER TABLE customers
DROP INDEX uq_customers_email;

Where SQL dialects differ

PostgreSQL: Syntax is DROP INDEX [CONCURRENTLY] [IF EXISTS] name [CASCADE | RESTRICT]. You can drop several indexes in one statement by separating names with commas, except with CONCURRENTLY. Use a schema-qualified name if the index is not on the search path.

MySQL: Requires ON table_name. You can also use ALTER TABLE ... DROP INDEX name. The primary key index is dropped with ALTER TABLE ... DROP PRIMARY KEY. DROP INDEX IF EXISTS is accepted by MariaDB, but not by MySQL 8.0.

SQL Server: Use DROP INDEX [IF EXISTS] index_name ON table_name. The IF EXISTS option was added in SQL Server 2016. Indexes created by PRIMARY KEY or UNIQUE constraints must be removed with ALTER TABLE DROP CONSTRAINT.

SQLite: Uses DROP INDEX [IF EXISTS] name. Indexes that SQLite created automatically for UNIQUE or PRIMARY KEY constraints cannot be dropped this way.

Oracle: Uses DROP INDEX index_name, with an optional schema prefix. An index tied to a constraint is dropped by dropping the constraint. Oracle does not support IF EXISTS before version 23ai.

Common mistakes

  • Using the wrong syntax for the database. MySQL and SQL Server need ON table_name, while PostgreSQL and SQLite must not have it.
  • Dropping an index that a query depends on without checking the plan first. Look at usage statistics and test the slow queries on a copy before dropping in production.
  • Trying to drop a primary key or unique constraint index directly. Remove the constraint with ALTER TABLE instead.
  • Dropping a foreign key column index in MySQL. InnoDB needs an index on the referencing column, and it will refuse the drop or use another index if one exists.
  • Running a blocking drop on a busy PostgreSQL table when CONCURRENTLY would avoid the lock.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator