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.
DROP INDEX IF EXISTS idx_orders_customer;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- 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 withDROP INDEX. - Dropping a table drops its indexes automatically.
- Check first that nothing needs it: find slow queries with
EXPLAINbefore 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.
Related
CREATE INDEX,DROP TABLE,EXPLAIN,ALTER TABLE- Lessons: Indexes, Query optimization