Covering index concept in SQL

Introduction

A covering index helps the database find all the data it needs from the index alone, making queries faster without looking at the main table.

When you want to speed up searches on specific columns without reading the full table.

When you often query a few columns together and want quick results.

When you want to reduce the time your database spends reading data from disk.

When you want to improve performance for reports that use the same columns repeatedly.

Syntax

SQL

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

Include all columns needed by your query in the index to make it a covering index.

The order of columns in the index can affect performance.

Examples

Creates an index on the 'name' and 'email' columns of the customers table. Queries selecting only these columns can use this index fully.

SQL

CREATE INDEX idx_customer_name_email ON customers (name, email);

This index covers queries filtering or selecting by order_date and status, speeding up those queries.

SQL

CREATE INDEX idx_orders_date_status ON orders (order_date, status);

Sample Program

This example creates a products table and inserts some rows. Then it creates a covering index on the name and category columns. The SELECT query only asks for name and category, so the database can use the index without reading the full table.

SQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(50),
  category VARCHAR(30),
  price DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Pen', 'Stationery', 1.20),
(2, 'Notebook', 'Stationery', 2.50),
(3, 'Coffee Mug', 'Kitchen', 5.00);

CREATE INDEX idx_products_name_category ON products (name, category);

-- Query that can use the covering index
SELECT name, category FROM products WHERE name = 'Pen';

Important Notes

A covering index can make queries much faster by avoiding extra data lookups.

Too many indexes can slow down data changes like INSERT or UPDATE, so add them carefully.

Covering indexes work best when queries select only the indexed columns.

Summary

A covering index includes all columns a query needs, so the database reads only the index.

It speeds up queries by reducing the need to access the main table data.

Create covering indexes on columns frequently used together in queries.