If you’ve got an index in a SQLite database that you no longer need, you should probably get rid of it. Keeping unused indexes can be a drag on database performance, especially if your data is being regularly updated.

This article shows you how to delete an index in SQLite.

Syntax

To delete an index in SQLite, we use the DROP INDEX statement. This goes something like this:

DROP INDEX index_name;

Where index_name is the name of the index.

We can use dot-notation to include the schema name if required, and we can also use an IF EXISTS clause to prevent getting an error in the event that the index doesn’t actually exist.

So our statement could become something like this:

DROP INDEX IF EXISTS schema_name.index_name;

Example

Suppose we use the .indexes dot command to list out all indexes:

.indexes

Output:

idx_employees_dept_last  idx_employees_last_name  idx_pets_petname

In my case there are three indexes.

Let’s drop one:

DROP INDEX idx_pets_petname;

That code dropped the idx_pets_petname index.

Verify the Result

Let’s run .indexes again to verify that it was dropped:

.indexes

Output:

idx_employees_dept_last  idx_employees_last_name

As expected, the index no longer exists.

Using the IF EXISTS Clause

As mentioned, we can use the IF EXISTS clause in order to suppress any errors that might otherwise be returned in the event that the specified index doesn’t exist.

First, let’s try dropping the index again, but without the IF EXISTS clause:

DROP INDEX idx_pets_petname;

Output:

Parse error: no such index: idx_pets_petname

We got an error because the specified index doesn’t exist.

Now let’s use the IF EXISTS clause:

DROP INDEX IF EXISTS idx_pets_petname;

Output:

That output is unintentionally blank. SQLite didn’t return anything (because it didn’t do anything). Importantly, it didn’t return an error.

Once an index has been dropped, it’s gone forever. The only way to get it back is to create it again (using the CREATE INDEX statement).