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, ...);
- Creates an index on one or more columns.
- Duplicate values are allowed.
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);
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';
SQL Index Drop Index Example
To remove an index, use the DROP INDEX statement. In SQLite, the syntax is:
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
