Remove an Index With DROP INDEX

Drop an index that is no longer useful so writes no longer have to maintain it.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Drop an unused status index and keep the table rows

Dropping the index removes only the lookup structure. The orders rows remain available, which the final SELECT confirms.

MySQL MariaDB SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, status VARCHAR(20));

INSERT INTO
  orders
VALUES
  (1, 'open'),
  (2, 'closed');

CREATE INDEX idx_orders_status ON orders (status);

Validated query

SQL

DROP INDEX idx_orders_status ON orders;

SELECT
  id,
  status
FROM
  orders
ORDER BY
  id;

Expected result

id	status
1	open
2	closed

PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, status VARCHAR(20));

INSERT INTO
  orders
VALUES
  (1, 'open'),
  (2, 'closed');

CREATE INDEX idx_orders_status ON orders (status);

Validated query

SQL

DROP INDEX idx_orders_status;

SELECT
  id,
  status
FROM
  orders
ORDER BY
  id;

Expected result

id	status
1	open
2	closed

MySQL and MariaDB name the table in `DROP INDEX`; PostgreSQL and SQLite drop by index name.

Useful when

Where this command helps.

removing an unused performance index after query patterns change
cleaning up duplicate or redundant indexes during schema maintenance

Explanation

What the command is doing.

DROP INDEX removes an index from a table while leaving the table data intact. Dropping an index can reduce write overhead and storage when the index no longer supports real queries. Do not drop indexes used to enforce primary keys or unique constraints unless you intend to remove that integrity rule too.

Remove an Index With DROP INDEX

Drop an unused status index and keep the table rows

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.