SQL Index

Indexes are used in SQL to speed up searches in tables. When you create an index on a column, the database creates a separate lookup structure to make data retrieval faster — especially for queries using WHERE, JOIN, or sorting.

In SQLite, indexes are automatically used by the query planner when they help performance. However, you don’t “see” them working — they’re behind the scenes.

SQL Index Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE INDEX index_name

ON table_name (column1, column2, ...);

• Creates an index on one or more columns.
• Duplicate values are allowed.

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);

• Creates a unique index.
• Ensures all values in the indexed column(s) are different.

SQL Index Example

This query creates an index on the last_name column in the Patients table:

CREATE INDEX idx_patients_lastname
ON Patients (last_name);

CREATE INDEX idx_patients_lastname

ON Patients (last_name);

This index helps queries that search for specific last names to execute faster. You can then run this query to use the index:

SELECT * FROM Patients
WHERE last_name = 'Smith';

SELECT * FROM Patients

WHERE last_name = 'Smith';

SQL Index Drop Index Example

To remove an index, use the DROP INDEX statement. In SQLite, the syntax is:

DROP INDEX idx_patients_lastname;

DROP INDEX idx_patients_lastname;

This deletes the index named idx_patients_lastname.

Note: In SQLite (like in our playground), indexes exist outside the table definition, so dropping an index does not affect the data in the table.

SQL Index Visual Diagram

SQL Index Labs