SQL Indexes — Speeding Up Data Retrieval

A school database has many students and often searches by city. Repeatedly scanning every record may become expensive.

An index is an additional data structure that can help a database locate or order matching records. The optimizer decides whether using it is worthwhile for a query.

CREATE INDEX describes indexed expressions or columns. The database maintains that structure as data changes, and may use it to find a smaller candidate set. The query’s logical answer remains the same whether the optimizer uses an index or scans the table.

Related concepts: Read Execution Plans and Choose Effective Indexes.

A SQL INDEX is a performance optimization structure. It allows the database to find and retrieve data faster — similar to how a book index helps locate chapters quickly.

• On columns frequently used in WHERE clauses
• On columns used in JOIN or ORDER BY
• For large datasets where performance matters

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A', 'Delhi'),
(2, 'Diya Iyer', '9B', 'Chennai'),
(3, 'Sneha Patil', '10A', 'Pune'),
(4, 'Mehul Agarwal', '9B', 'Delhi'),
(5, 'Saira Bano', '8C', 'Hyderabad');

SQL · portable concepts; preserved examples identify their engine

Let’s create an index on the city column since we often search students by city:

CREATE INDEX idx_city
ON students(city);

SQL · portable concepts; preserved examples identify their engine

The index supplies a possible access path. Inspect EXPLAIN and measure representative data to learn whether this query uses it and benefits from it.:

SELECT name, class
FROM students
WHERE city = 'Delhi';

name           | class
----------------+-------
Aarav Sharma   | 10A
Mehul Agarwal  | 9B

SQL · portable concepts; preserved examples identify their engine

Create an index on both class and city for combined filters:

CREATE INDEX idx_class_city
ON students(class, city);

This helps with queries like:

SELECT name
FROM students
WHERE class = '9B' AND city = 'Delhi';

name
----------
Mehul Agarwal

SQL · portable concepts; preserved examples identify their engine

Indexes can also enforce uniqueness (aside from primary keys):

CREATE UNIQUE INDEX idx_unique_name
ON students(name);

This prevents duplicate names (useful if names must be unique in your context).

Dialect distinction: the preserved DROP INDEX idx_city; form works in SQLite and PostgreSQL. On MySQL 8.4, use DROP INDEX idx_city ON students;. The index is recreated in the setup above before either drop variant is attempted.

SQL · portable concepts; preserved examples identify their engine

DROP INDEX idx_city;

• Indexes improve SELECT performance, but slow down INSERT/UPDATE/DELETE a bit.
• Use only where needed — don’t blindly index every column.
• Use EXPLAIN (in MySQL/PostgreSQL) to see if your index is being used.

The school admin needs to quickly list students by city and class — indexing those columns speeds up daily reporting, especially when thousands of students are enrolled.

Indexes use storage and add work to writes. A low-selectivity filter or tiny table may make a scan a good choice. Index existence does not prove usage; inspect the engine’s execution plan and measure representative workloads.

Create a city index and read the Pune students. What should change about the logical answer?

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

CREATE INDEX practice_city_index ON practice_students(city);
SELECT name FROM practice_students WHERE city = 'Pune' ORDER BY roll_no;

The index offers a possible access path. It must not change which city values match or which names are logically returned.

An index is an additional data structure that can help a database locate or order matching records. The optimizer decides whether using it is worthwhile for a query.

The index offers a possible access path. It must not change which city values match or which names are logically returned.

Continue your understanding with Read Execution Plans and Choose Effective Indexes.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.