Index Optimization — Boost Query Speed with Smart Indexing

Reports often filter by class and then sort marks. Several single-column indexes may not serve that combined access pattern as well as an appropriate composite index.

Index optimization chooses a small, useful set of indexes for actual filters, joins and ordering needs, while accounting for write and storage costs.

Column order matters in a composite B-tree index. Its leading columns influence which predicates can use an efficient lookup. A covering index can provide needed values without an additional base-row lookup for supported plans. Measure alternatives rather than adding every possible combination.

Related concepts: Indexes and Read Execution Plans.

An index is a database object that speeds up data retrieval operations. It is built on one or more columns of a table. Indexes allow the database to find rows efficiently without scanning the entire table.

• Primary Index – Automatically created on primary key
• Unique Index – Prevents duplicates in specific columns
• Composite Index – Built on two or more columns
• Covering Index – Index that includes all required columns for a query

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)
);

SQL · portable concepts; preserved examples identify their engine

-- Without index
EXPLAIN SELECT * FROM students WHERE class = '10A';

-- Add index
CREATE INDEX idx_class ON students(class);

-- Now re-run
EXPLAIN SELECT * FROM students WHERE class = '10A';

Before: type = ALL (full scan)  
After:  type = ref (uses index)

SQL · portable concepts; preserved examples identify their engine

If you frequently filter by both class and city together:

CREATE INDEX idx_class_city ON students(class, city);

Important: The order matters. This index helps:

SELECT * FROM students WHERE class = '10A' AND city = 'Pune';

But not:

SELECT * FROM students WHERE city = 'Pune'; -- Won’t use the index efficiently

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE marks (
  roll_no INT,
  subject VARCHAR(30),
  marks INT,
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

-- Index the join column
CREATE INDEX idx_marks_roll_no ON marks(roll_no);

-- Query using join
EXPLAIN SELECT s.name, m.subject, m.marks
FROM students s
JOIN marks m ON s.roll_no = m.roll_no;

Both tables use indexed access for join

SQL · portable concepts; preserved examples identify their engine

-- If query needs only these columns:
SELECT class, city FROM students WHERE class = '10A';

-- Create a covering index
CREATE INDEX idx_class_city_only ON students(class, city);

This helps the query get all its data directly from the index, skipping table lookup — improving speed.

• On columns with few unique values (e.g., gender, boolean)
• On small tables (indexes won’t help much)
• For frequently updated columns — indexing slows down INSERT/UPDATE

SQL · portable concepts; preserved examples identify their engine

EXPLAIN SELECT ... -- See if index is being used

SHOW INDEX FROM students; -- Check index usage

• Drop unused indexes using DROP INDEX
• Use ANALYZE TABLE to update index stats
• Test new indexes on staging before production

• Index roll_no, class in student searches
• Composite index on (subject, marks) for report generation
• Covering index on (class, city) for location-wise filtering

Leftmost-prefix behavior is a useful basic model, with engine-specific exceptions and optimizer features. Functions, collation, type conversion and expression indexes can alter eligibility. Extra indexes make insertions and updates more expensive.

Create a class/marks index, then run the class report with the same logical result.

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_class_marks_index ON practice_students(class, marks);
SELECT name, marks FROM practice_students WHERE class = '10A' ORDER BY marks DESC, roll_no;

The index offers a class-first access path. Whether this fixture uses it is an observation from the plan, not a promised outcome of creating it.

Index optimization chooses a small, useful set of indexes for actual filters, joins and ordering needs, while accounting for write and storage costs.

The index offers a class-first access path. Whether this fixture uses it is an observation from the plan, not a promised outcome of creating it.

Continue your understanding with Indexes and Read Execution Plans.

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