Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Data Definition

SQL DROP INDEX statement

Reference for SQL DROP INDEX: remove an index you no longer need, IF EXISTS, why the syntax differs between MySQL, SQL Server, PostgreSQL and Oracle.

DROP INDEX deletes an index. The table and its rows are untouched; queries keep returning the same results, only the way the database finds rows changes. Removing an unused index makes writes cheaper and frees space.

Syntax is one of the places where dialects disagree: MySQL and SQL Server need to know the table, while SQLite, PostgreSQL and Oracle identify the index by its name alone.

SQL
DROP INDEX IF EXISTS idx_orders_customer;

Try it

SQL Create, list and drop an index ยท database: shop

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

SQL Constraint indexes cannot be dropped (fails on purpose) ยท database: shop

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

Syntax

SQL
DROP INDEX [IF EXISTS] index_name;              -- SQLite, PostgreSQL
DROP INDEX index_name ON table_name;            -- MySQL, SQL Server (IF EXISTS in 2016+)
Part Description
IF EXISTS No error if the index is missing.
index_name The index to remove, optionally schema-qualified.
ON table_name MySQL and SQL Server only: the table that owns the index.

Dialect differences

Dialect Syntax
SQLite DROP INDEX [IF EXISTS] name; Index names are unique across the database.
PostgreSQL DROP INDEX [CONCURRENTLY] [IF EXISTS] name; CONCURRENTLY avoids blocking writes.
MySQL DROP INDEX name ON table; or ALTER TABLE t DROP INDEX name;
SQL Server DROP INDEX name ON table; (older form: DROP INDEX table.name).
Oracle DROP INDEX name; Indexes backing constraints go away when the constraint is dropped.
SQL
-- MySQL
DROP INDEX idx_orders_customer ON orders;

-- PostgreSQL
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer;

Usage notes

  • Constraint indexes (primary key, UNIQUE) are removed by dropping or altering the constraint, not with DROP INDEX.
  • Dropping a table drops its indexes automatically.
  • Check first that nothing needs it: find slow queries with EXPLAIN before and after, and look at index usage statistics where your database offers them.
  • Foreign keys: MySQL may refuse to drop an index a foreign key relies on.
  • Cheap to undo for small tables, expensive for big ones: recreating an index on millions of rows can take a long time.
  • SQLite file size is not reduced until VACUUM.