Creating and Managing SQL Indexes

In the previous reading, you explored how using a SQL index can boost lookup efficiency. Here, you’ll learn the syntax for creating, removing, and inspecting indexes—primarily in SQLite3 (with notes applicable to other SQL dialects like Postgres or MySQL).

By the end, you’ll know how to:

Create an index on one or more columns
Set a UNIQUE constraint (which under-the-hood uses an index) on one or more columns
Drop an index you no longer need
Search for indexes on a database or table

Create a SQL Index

The general syntax for creating an index in SQLite3 is:

CREATE INDEX index_name ON table_name (column_list);

Some conventions for naming your index might include:

idx_[table_name]_[column_names] – e.g., idx_users_email
column_list is a comma-separated list of columns you want to index

Example: Indexing Multiple Columns

Let’s revisit the bakers and cookies schema from before:

CREATE TABLE bakers (
  id INT PRIMARY KEY,
  full_name TEXT
);

CREATE TABLE cookies (
  id INT PRIMARY KEY,
  name TEXT,
  baker_id INT REFERENCES bakers(id),
  type TEXT,
  chocolate BOOLEAN
);

Suppose you want to query often by the type, chocolate, and baker_id columns. You could create an index like so:

CREATE INDEX
    idx_cookies_type_chocolate_baker_id   -- index name
    ON cookies(type, chocolate, baker_id);

Now queries filtering by type or by type + other columns can leverage this index for faster lookups (O(log n) rather than O(n)).

First Column Priority

When you index multiple columns, the first column in the (column_list) is especially important. This is because the index is ordered primarily by that column. For instance, if you put type first, queries that filter on type will benefit from the index.

Queries like:

SELECT * FROM cookies
WHERE type = 'sugar' AND baker_id = 1;

will use idx_cookies_type_chocolate_baker_id. But if you query only by baker_id (and type isn’t in the WHERE clause), the database will not use this index. You might consider a separate index if that query pattern is frequent.

Create a UNIQUE Constraint via an Index

You’ve seen how adding UNIQUE to a column in a CREATE TABLE statement automatically creates an index behind the scenes. Alternatively, you can explicitly define a UNIQUE index:

CREATE UNIQUE INDEX index_name ON table_name (column_list);

For example, if you forgot to mark full_name as UNIQUE in bakers, you can do:

CREATE UNIQUE INDEX idx_bakers_full_name ON bakers(full_name);

Unique Across Multiple Columns

Sometimes you only want to prevent duplication of a combination of columns. For instance, you might not allow a single baker_id to have the same type and chocolate values repeated. In that case:

CREATE UNIQUE INDEX idx_cookies_baker_id_type_chocolate
  ON cookies(baker_id, type, chocolate);

This means no two rows in cookies can share the same three-column combination, yet you can still have multiple cookies of the same type or chocolate if they differ by baker_id or any other attribute.

Remove a SQL Index

If an index no longer serves your needs or creates too much overhead, you can remove it:

DROP INDEX idx_cookies_type_chocolate;

This might be necessary if you realize the index doesn’t improve overall performance (for instance, if insertion/deletion overhead is too high or if queries rarely use the index).

Searching for Index Names

In SQLite3, you can list all indexes in your database or in a specific table:

-- All indexes in the entire database
sqlite> .indexes

-- Indexes for a specific table
sqlite> .indexes bakers

-- Indexes matching a pattern (e.g., part of the name)
sqlite> .indexes %cookie%

This can be very handy if you have many indexes and need to see if you’ve already created one or recall its exact name.

What Happens If You Alter a Table?

If you drop a column that’s part of an index, SQLite will remove that column from the index definition but keep the rest of the index intact. For example, if type was in your idx_cookies_baker_id_type_chocolate, dropping type from the table would automatically result in:

CREATE UNIQUE INDEX idx_cookies_baker_id_type_chocolate
  ON cookies(baker_id, chocolate);

So be mindful of how your indexes might change if you modify table columns.

Wrapping Up

Creating an Index: CREATE INDEX idx_table_col ON table(col1, col2); speeds up lookups.
Defining a UNIQUE Constraint: You can create a UNIQUE index for one or many columns, preventing duplicates.
Dropping an Index: DROP INDEX index_name; if it’s no longer beneficial.
Inspecting Indexes: Use .indexes in SQLite to see what's defined.
Altering Tables: Dropping a column automatically removes it from any associated index.

By carefully using indexes on columns you query frequently—and dropping them if they prove unnecessary—you’ll keep your database queries efficient without incurring extra insertion overhead.